Disconection of Database users in mirror configuration

On the machine DBSP01 we have the TEST database running. In the SQL environment we created the user Test. This user was set up to be the database owner of the TEST database. To make a connection to the Test database, the user Test was used and everything works perfect.

On the machine DBSP02 we also created a database called TEST and a user called Test, who was database owner of the TEST database. Again we where able to connect to the database by using the user Test.

Now we set up a so called mirror configuration. This means that we have one active database, called the principal database, and a standby database, called the mirror. The active database is used to communicate to the outside, while the standby database is copying every change from the active database. This way the principal database and the mirror database contain always the same data. When one of the databases is acting as a mirror database, this database cannot be reached from the outside.

After a while it can happen that the principal database fails for some reason. The database is not capable to run properly, so it stops. The mirror database will become the principal database and the outside can connect to the new principal database. Here we have a problem: the user Test is no longer the database owner of the TEST database. For some reason the user Test is disconnected as database owner of the TEST database when the database is acting as mirror. I can’t find the reason for this problem and I can also not find a solution for this problem.

Hi,

I quoted the topic "Managing server metadata and jobs after role switching" in BOL.

"For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.

The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.

If a user is orphaned on the new principal, create the login on the new principal and run sp_change_users_login (Transact-SQL). For more information, see Troubleshooting Orphaned Users. "

Peng

Disconection of Database users in mirror configuration

On the machine DBSP01 we have the TEST database running. In the SQL environment we created the user Test. This user was set up to be the database owner of the TEST database. To make a connection to the Test database, the user Test was used and everything works perfect.

On the machine DBSP02 we also created a database called TEST and a user called Test, who was database owner of the TEST database. Again we where able to connect to the database by using the user Test.

Now we set up a so called mirror configuration. This means that we have one active database, called the principal database, and a standby database, called the mirror. The active database is used to communicate to the outside, while the standby database is copying every change from the active database. This way the principal database and the mirror database contain always the same data. When one of the databases is acting as a mirror database, this database cannot be reached from the outside.

After a while it can happen that the principal database fails for some reason. The database is not capable to run properly, so it stops. The mirror database will become the principal database and the outside can connect to the new principal database. Here we have a problem: the user Test is no longer the database owner of the TEST database. For some reason the user Test is disconnected as database owner of the TEST database when the database is acting as mirror. I can’t find the reason for this problem and I can also not find a solution for this problem.

Hi,

I quoted the topic "Managing server metadata and jobs after role switching" in BOL.

"For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.

The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.

If a user is orphaned on the new principal, create the login on the new principal and run sp_change_users_login (Transact-SQL). For more information, see Troubleshooting Orphaned Users. "

Peng

Disarming IDENTITY generation

Ok, suppose I have created a new table with an Identity column, but I want to load the table from an existing table and I can’t let SQL Ev reset the Identity values. In SQL Server I can turn off Identity generation using

SET IDENTITY_INSERT biblio.dbo.authors OFF

Is there an equivalent in SQL Ev?

My next question is (if this does not work), can I use ALTER TABLE to disarm the Identity value and re-arm it after the load?

Bill,

no, you cannot disable the generation of identity values as you insert records into a table with an identity column, but you can alter the seed and increment values on the column using ALTER TABLE ALTER COLUMN standard syntax. For example, if the rows you are putting into the empty table begin with identity values starting at 1000, just create the table with this identity columns seed set to 1000.

if you want to know the last value of an identity column after one or more inserts, select the @.@.IDENTITY function against the table.

if there is any chance this table will be in an RDA or merge replication synchronization relationship with a server, I would strongly suggest you use uniqueidentifiers instead of identity columns on your table. this will avoid a lot of admin and possible conflicts if you are in a multi-user environment.

as I follow your posts, it sure sounds to me like you are in the process of creating a starter SQL Everywhere database based on a server-side database. Have you considered just setting up a quick merge replication and using the option to have the SQL Everywhere database created dynamically from the server upon initial replication (AddSubscription(AddOption.CreateDatabase))? I have posted a lot of info on this and done a few MSDN webcasts on the topic, so search this forum for 'starter database' for more details. this would solve your issues with identity column values and also with FK creation.

Darren

|||

Actually, I'm trying to write an EBook to supplement my new Hitchhiker's Guide to Visual Studio and SQL Server book. So far, all I've found is a million issues...

The current problem is that the ID values in the rows on the source table are joined logically to other tables. That is, the Author_ID is in the Title_Author table so it can't be arbitrarily changed without changing the other table(s) that are linked to the Author_ID. The same goes for the other tables. This makes it tough to load Identity values at all into a SQLCE database--I guess I'll have to give up on that approach. However, I don't see that using uniqueidentifiers will help unless I can go back and rebuild the PK/FK relationships on the source tables using them (a PIA).

Yes, I can see that I need to go back two squares and try to build a replication scenario.

My inital tests with SSIS did not work so that's a non-starter. I found that the host database uses datatypes not supported by SQL Ev including VarChar and Timestamp which chokes SSIS.

I expect I'll have to create a custom server-side database that's specifically designed to deal with all of these issues.

I'll check out your content. I would like to check out the SQL CF MVP pages (I'm also an MVP) can you drop a private note pointing to where you hang out?

Bill

|||

sure Bill - feel free to shoot me an email - I'm googlable and in the MVP directory as well.

-ds

|||

My two cents... With SQL CE 2.0 you could actually overwite the identity values through the OLE DB provider (a bit like in Access but this engine goes a bit further and even updates the seed). Microsoft dropped this behavior with SQL Mobile and this posed a big challenge for my database copy / sync tools. What now? Turns out, the only solution is to ALTER TABLE and set the seed to the next value to be inserted for every row. You will also have to reset the identity properties to what they are supposed to be at the end of the export procedure. To make all of this work, you also have to make sure you close the base table cursor (if any) before altering the identity value. This means more work for a SqlCeResultSet, but not for an INSERT command.

It's a lot of development work but the code is not slower than in the SQL CE 2.0 case, so it's quite workable and it is the solution I actually adopted.

|||

Interesting solution. I guess it would only be more convoluted if it involved live chickens.

I'm not sure I'll recommend this in my book other than mention it as an alternative...

Thanks

|||

I don't think that was going to work because the SQL Server 2005 Mobile Edition does not support Alter table as a SQL Statement .At leat I could not make it work.Right now I'm stucked on that problem and I would like to read other opinions

Nelson

|||

I would like to know how can I make it work using the alter table statement since I tried and it did not work.I tried using the SQL Server Editor in Visual Studio and at runtime .None of those options seems to work.

Thanks in advance

Disarming IDENTITY generation

Ok, suppose I have created a new table with an Identity column, but I want to load the table from an existing table and I can’t let SQL Ev reset the Identity values. In SQL Server I can turn off Identity generation using

SET IDENTITY_INSERT biblio.dbo.authors OFF

Is there an equivalent in SQL Ev?

My next question is (if this does not work), can I use ALTER TABLE to disarm the Identity value and re-arm it after the load?

Bill,

no, you cannot disable the generation of identity values as you insert records into a table with an identity column, but you can alter the seed and increment values on the column using ALTER TABLE ALTER COLUMN standard syntax. For example, if the rows you are putting into the empty table begin with identity values starting at 1000, just create the table with this identity columns seed set to 1000.

if you want to know the last value of an identity column after one or more inserts, select the @.@.IDENTITY function against the table.

if there is any chance this table will be in an RDA or merge replication synchronization relationship with a server, I would strongly suggest you use uniqueidentifiers instead of identity columns on your table. this will avoid a lot of admin and possible conflicts if you are in a multi-user environment.

as I follow your posts, it sure sounds to me like you are in the process of creating a starter SQL Everywhere database based on a server-side database. Have you considered just setting up a quick merge replication and using the option to have the SQL Everywhere database created dynamically from the server upon initial replication (AddSubscription(AddOption.CreateDatabase))? I have posted a lot of info on this and done a few MSDN webcasts on the topic, so search this forum for 'starter database' for more details. this would solve your issues with identity column values and also with FK creation.

Darren

|||

Actually, I'm trying to write an EBook to supplement my new Hitchhiker's Guide to Visual Studio and SQL Server book. So far, all I've found is a million issues...

The current problem is that the ID values in the rows on the source table are joined logically to other tables. That is, the Author_ID is in the Title_Author table so it can't be arbitrarily changed without changing the other table(s) that are linked to the Author_ID. The same goes for the other tables. This makes it tough to load Identity values at all into a SQLCE database--I guess I'll have to give up on that approach. However, I don't see that using uniqueidentifiers will help unless I can go back and rebuild the PK/FK relationships on the source tables using them (a PIA).

Yes, I can see that I need to go back two squares and try to build a replication scenario.

My inital tests with SSIS did not work so that's a non-starter. I found that the host database uses datatypes not supported by SQL Ev including VarChar and Timestamp which chokes SSIS.

I expect I'll have to create a custom server-side database that's specifically designed to deal with all of these issues.

I'll check out your content. I would like to check out the SQL CF MVP pages (I'm also an MVP) can you drop a private note pointing to where you hang out?

Bill

|||

sure Bill - feel free to shoot me an email - I'm googlable and in the MVP directory as well.

-ds

|||

My two cents... With SQL CE 2.0 you could actually overwite the identity values through the OLE DB provider (a bit like in Access but this engine goes a bit further and even updates the seed). Microsoft dropped this behavior with SQL Mobile and this posed a big challenge for my database copy / sync tools. What now? Turns out, the only solution is to ALTER TABLE and set the seed to the next value to be inserted for every row. You will also have to reset the identity properties to what they are supposed to be at the end of the export procedure. To make all of this work, you also have to make sure you close the base table cursor (if any) before altering the identity value. This means more work for a SqlCeResultSet, but not for an INSERT command.

It's a lot of development work but the code is not slower than in the SQL CE 2.0 case, so it's quite workable and it is the solution I actually adopted.

|||

Interesting solution. I guess it would only be more convoluted if it involved live chickens.

I'm not sure I'll recommend this in my book other than mention it as an alternative...

Thanks

|||

I don't think that was going to work because the SQL Server 2005 Mobile Edition does not support Alter table as a SQL Statement .At leat I could not make it work.Right now I'm stucked on that problem and I would like to read other opinions

Nelson

|||

I would like to know how can I make it work using the alter table statement since I tried and it did not work.I tried using the SQL Server Editor in Visual Studio and at runtime .None of those options seems to work.

Thanks in advance

Disappearing data elements in the header

I have created a number of reports where I need to display database elements in the Header record. I have been using the reportitems feature to get them to display there. When I preview the report the header looks fine; however, when the report is exported to pdf or printed out the header information completly disappears.

Any suggestions would be great.

Hi,

While displaying the data fileds in the Header you are using report items and using those in the text box of the Report header then the textbox has the Repeat With property.Set it to the data region with which the report item need to be repeated.Previously I tried this it is looking good in the preview When i am exporting the data fileds are displaying in the first page only not displaying in the consecutive pages of report.Can you try by setting the Repeat with property.

Thanks.

|||

Using RepeatWith property is not an ideal solution because the cause for a new page creation need not necessarily be the data region that spans more than 1 page. So create a new hidden report parameter and have the default value of that as the one which you have used in your hidden report item.

Shyam

Disabling print and export options

Hi,

Obviusly RS wasn't created for this, but i have to ask.

It's posible disable print and export buttons for specific reports?
For example, reports in a specific directory.

Thanks.

Hi Dark_V

It is possible to hide the Toolbar. You can use the following as your url

The following example hides the HTML Viewer toolbar by setting therc:Toolbar parameter value tofalse:

http://servername/reportserver?/ReportName&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false

|||

l0n3i200n wrote:

Hi Dark_V

It is possible to hide the Toolbar. You can use the following as your url

The following example hides the HTML Viewer toolbar by setting therc:Toolbar parameter value tofalse:

http://servername/reportserver?/ReportName&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false

Although, this is the best option I can think of, I just want to pointout that the user can remove the toolbar=false part to still access thetoolbar. I don't think there's any capability to keep the user fromprinting because they can still just print the page from the browser'smenu.

Disabling print and export options

Hi,

Obviusly RS wasn't created for this, but i have to ask.

It's posible disable print and export buttons for specific reports?
For example, reports in a specific directory.

Thanks.

This is not possible using the default viewer. You can hide or disable them entirely but having this logic would require you building your own viewer.

|||

Thanks,

I can't find any component or viewer code with this features.

Someone find something with this functions?

Thanks

Disabling drill down functionality for a column

Hi,

I am creating an adhoc report using report builde in RS2005, now the created report has few columns and some if them have functonality of drillng diwn but is it possible to have a column where a particular column can not be drilled into >

prashant

i am sorry ...it was easy ..i found it out myself its in VS

Disable Trigger rules

I had created several Trigger rules in current database and it will be
active on 1st Nov,2005,
Can I disable them now ? and then make them active on that target date '
Thanks a lotThe only way to disable a trigger is to drop it (or comment out the
T-SQL code in it so it does nothing). If I were you I'd script them out
to a file (with SQLEM), drop them (thereby "disabling" them) and then
schedule the SQL script in the file containing all the trigger code to
run via SQLAgent on Nov 1st (thereby "re-enabling" them).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Agnes wrote:

>I had created several Trigger rules in current database and it will be
>active on 1st Nov,2005,
>Can I disable them now ? and then make them active on that target date '
>Thanks a lot
>
>|||Thanks Mike, I try your method , script it out. (however, it seems I must sc
ript the table structure too.).Any Simple method, i can script trigger rule
only , and the drop them all '
Please be kind to provide some sample .. Thanks in advance. (Today is my 4th
day working about Trigger rule)
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> '?:%23B7WU132FHA.279
6@.tk2msftngp13.phx.gbl...
The only way to disable a trigger is to drop it (or comment out the T-SQL co
de in it so it does nothing). If I were you I'd script them out to a file (
with SQLEM), drop them (thereby "disabling" them) and then schedule the SQL
script in the file containing all the trigger code to run via SQLAgent on No
v 1st (thereby "re-enabling" them).
mike hodgson
blog: http://sqlnerd.blogspot.com
Agnes wrote:
I had created several Trigger rules in current database and it will be
active on 1st Nov,2005,
Can I disable them now ? and then make them active on that target date '
Thanks a lot|||Hey you have soemthing called
ALTER TABLE ENABLE/DISABLE TRIGGER Read in BOL
If you want to do it for all tables, use
sp_Msforeachtable "ALTER TABLE '?' DISABLE TRIGGER"
Regards
R.D
--Knowledge gets doubled when shared
"Agnes" wrote:

> Thanks Mike, I try your method , script it out. (however, it seems I must
script the table structure too.).Any Simple method, i can script trigger rul
e only , and the drop them all '
> Please be kind to provide some sample .. Thanks in advance. (Today is my 4
th day working about Trigger rule)
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> '?:%23B7WU132FHA
.2796@.tk2msftngp13.phx.gbl...
> The only way to disable a trigger is to drop it (or comment out the T-SQL code i
n it so it does nothing). If I were you I'd script them out to a file (with SQLEM),
drop them (thereby "disabling" them) and then schedule the SQL script in the file c
ont
aining all the trigger code to run via SQLAgent on Nov 1st (thereby "re-enabling" them).[co
lor=darkred]
> --
> mike hodgson
> blog: http://sqlnerd.blogspot.com
>
> Agnes wrote:
> I had created several Trigger rules in current database and it will be
> active on 1st Nov,2005,
> Can I disable them now ? and then make them active on that target date '
> Thanks a lot
>
>[/color]|||> The only way to disable a trigger is to drop it (or comment out the
> T-SQL code in it so it does nothing).
This is simply not true. Disabling or enabling a trigger is done through the
ALTER TABLE statement.
- to disable a trigger:
ALTER TABLE <table>
DISABLE TRIGGER <trigger>
- to enable a trigger:
ALTER TABLE <table>
ENABLE TRIGGER <trigger>
More here:
http://msdn.microsoft.com/library/d...br />
3ied.asp
ML|||Well you learn something new every day (I don't claim to know
everything). Thanks for the tip - that one's filed away in permanent
cache now. You know, over 10 years working with MSSQL and I'd never
noticed that clause in the ALTER TABLE statement.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
R.D wrote:

>Hey you have soemthing called
>ALTER TABLE ENABLE/DISABLE TRIGGER Read in BOL
>If you want to do it for all tables, use
>sp_Msforeachtable "ALTER TABLE '?' DISABLE TRIGGER"
>
>|||I myself was reluctant to use these two methods at first since QA does not
recognize ENABLE and DISABLE as reserved keywords. :)
ML|||Maybe my brain's just not wired correctly, but wouldn't it make more
sense to have the ENABLED/DISABLED clause as part of the ALTER TRIGGER
statement rather than the ALTER TABLE statement? Something like "ALTER
TRIGGER MyTrig ON me.MyTable DISABLE"
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ML wrote:

>I myself was reluctant to use these two methods at first since QA does not
>recognize ENABLE and DISABLE as reserved keywords. :)
>
>ML
>|||Quite the opposite! Wouldn't it make much more sense if triggers were added
to the objects:
alter table / view <name>
add trigger <trigger_name>
:)
After all - each trigger can only belong to a single table/view...
ML|||True, a trigger can only belong to a single table/view. But ALTER TABLE
is what you use to change the behaviour of an existing trigger (trigger
code, encryption, etc.), so it makes more sense to me that to change an
"attribute" of a trigger, which I would consider its "enabled/disabled"
status to be, you'd use ALTER TRIGGER.
Moot point anyway - purely a hypothetical, but I think the way they've
gone with T-SQL in SQL 2005 is better (with DISABLE TRIGGER being a DDL
statement all of its own).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ML wrote:

>Quite the opposite! Wouldn't it make much more sense if triggers were added
>to the objects:
>alter table / view <name>
> add trigger <trigger_name>
>:)
>After all - each trigger can only belong to a single table/view...
>
>ML
>

Disable the use of Enterprise Manager

I have created a new DB user, ABC, for using ASP.NET application to
connecting to SQL Server. Does it means this ABC user can logon in the
Enterprise Manager to view the data without using the web application' if
so, is there any way to block this ABC user from viewing data in Enterprise
Manager?> I have created a new DB user, ABC, for using ASP.NET application to
> connecting to SQL Server. Does it means this ABC user can logon in the
> Enterprise Manager to view the data without using the web application'
Yes.
> if
> so, is there any way to block this ABC user from viewing data in Enterprise
> Manager?
No. I suggest you use stored procedures and only allow your users to execute the stored procedures.
You might also want to read about application roles...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Grey" <erickwyum@.i-cable.com> wrote in message news:eMZrdqsnEHA.1160@.tk2msftngp13.phx.gbl...
> I have created a new DB user, ABC, for using ASP.NET application to
> connecting to SQL Server. Does it means this ABC user can logon in the
> Enterprise Manager to view the data without using the web application' if
> so, is there any way to block this ABC user from viewing data in Enterprise
> Manager?
>|||TO add to Tibor's comments, if you really, really want that capability, use
Application roles. They allow you to give permission when access is via the
application, but the end user does NOT have a permissions, so he can not go
through SEM or query analyzer and see anything.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Grey" <erickwyum@.i-cable.com> wrote in message
news:eMZrdqsnEHA.1160@.tk2msftngp13.phx.gbl...
> I have created a new DB user, ABC, for using ASP.NET application to
> connecting to SQL Server. Does it means this ABC user can logon in the
> Enterprise Manager to view the data without using the web application' if
> so, is there any way to block this ABC user from viewing data in
Enterprise
> Manager?
>

Disable the use of Enterprise Manager

I have created a new DB user, ABC, for using ASP.NET application to
connecting to SQL Server. Does it means this ABC user can logon in the
Enterprise Manager to view the data without using the web application? if
so, is there any way to block this ABC user from viewing data in Enterprise
Manager?
> I have created a new DB user, ABC, for using ASP.NET application to
> connecting to SQL Server. Does it means this ABC user can logon in the
> Enterprise Manager to view the data without using the web application?
Yes.

> if
> so, is there any way to block this ABC user from viewing data in Enterprise
> Manager?
No. I suggest you use stored procedures and only allow your users to execute the stored procedures.
You might also want to read about application roles...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Grey" <erickwyum@.i-cable.com> wrote in message news:eMZrdqsnEHA.1160@.tk2msftngp13.phx.gbl...
> I have created a new DB user, ABC, for using ASP.NET application to
> connecting to SQL Server. Does it means this ABC user can logon in the
> Enterprise Manager to view the data without using the web application? if
> so, is there any way to block this ABC user from viewing data in Enterprise
> Manager?
>
|||TO add to Tibor's comments, if you really, really want that capability, use
Application roles. They allow you to give permission when access is via the
application, but the end user does NOT have a permissions, so he can not go
through SEM or query analyzer and see anything.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Grey" <erickwyum@.i-cable.com> wrote in message
news:eMZrdqsnEHA.1160@.tk2msftngp13.phx.gbl...
> I have created a new DB user, ABC, for using ASP.NET application to
> connecting to SQL Server. Does it means this ABC user can logon in the
> Enterprise Manager to view the data without using the web application? if
> so, is there any way to block this ABC user from viewing data in
Enterprise
> Manager?
>

disable publishing and distribution error

Ok, which database are you creating sysmergepublications and
sysmergesubscriptions in ?
I've created sysmergesubscriptions in distribution , master , msdb ...
I still receive an error when I run
use master
exec sp_dropdistributor @.no_checks = 1
go
I receive :
Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%233PUslE$GHA.896@.TK2MSFTNGP03.phx.gbl...
> create table sysmergepublications
> (
> publisher sysname,
> publisher_db sysname,
> name sysname,
> description nvarchar(510),
> retention int,
> publication_type tinyint,
> pubid uniqueidentifier,
> designmasterid uniqueidentifier,
> parentid uniqueidentifier,
> sync_mode tinyint,
> allow_push int,
> allow_pull int,
> allow_anonymous int,
> centralized_conflicts int,
> status tinyint,
> snapshot_ready tinyint,
> enabled_for_internet bit,
> dynamic_filters bit,
> snapshot_in_defaultfolder bit,
> alt_snapshot_folder nvarchar(510),
> pre_snapshot_script nvarchar(510),
> post_snapshot_script nvarchar(510),
> compress_snapshot bit,
> ftp_address sysname,
> ftp_port int,
> ftp_subdirectory nvarchar(510),
> ftp_login sysname,
> ftp_password nvarchar(1048),
> conflict_retention int,
> keep_before_values int,
> allow_subscription_copy bit,
> allow_synctoalternate bit,
> validate_subscriber_info nvarchar(1000),
> ad_guidname sysname,
> backward_comp_level int,
> max_concurrent_merge int,
> max_concurrent_dynamic_snapshots int,
> use_partition_groups smallint,
> dynamic_filters_function_list nvarchar(1000),
> partition_id_eval_proc sysname,
> publication_number smallint,
> replicate_ddl int,
> allow_subscriber_initiated_snapshot bit,
> distributor sysname,
> snapshot_jobid binary(16),
> allow_web_synchronization bit,
> web_synchronization_url nvarchar(1000),
> allow_partition_realignment bit,
> retention_period_unit tinyint,
> decentralized_conflicts int,
> generation_leveling_threshold int,
> automatic_reinitialization_policy bit
> )
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:%23exvteE$GHA.4196@.TK2MSFTNGP03.phx.gbl...
>
Publication database. Check which databases are published for merge
replication and put it there.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uyEq9AQBHHA.4672@.TK2MSFTNGP02.phx.gbl...
> Ok, which database are you creating sysmergepublications and
> sysmergesubscriptions in ?
> I've created sysmergesubscriptions in distribution , master , msdb ...
> I still receive an error when I run
> use master
> exec sp_dropdistributor @.no_checks = 1
> go
> I receive :
> Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
> Invalid object name 'dbo.sysmergesubscriptions'.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%233PUslE$GHA.896@.TK2MSFTNGP03.phx.gbl...
>
|||My database is no longer being published for replication because I used
exec sp_replicationdboption 'databasename','merge publish',false
Nonetheless, I added a dbo.sysmergesubscriptions table to this database and
ran
use master
exec sp_dropdistributor @.no_checks = 1
go
but I still receive the error :
Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.
Could it be because I need to add dbo.sysmergesubscriptions as a system
object ? How do I do this ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eZifFJQBHHA.2304@.TK2MSFTNGP02.phx.gbl...
> Publication database. Check which databases are published for merge
> replication and put it there.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:uyEq9AQBHHA.4672@.TK2MSFTNGP02.phx.gbl...
>
|||these are the lines around 103
if not exists (select * from dbo.sysmergesubscriptions
where UPPER(subscriber_server) =
UPPER(publishingservername()) and db_name = db_name() and subid <> pubid)
begin
select @.ignore_merge_metadata = 1
end
It is complaining about the database you are running the command in. Is the
table there? Does it have the owner dbo?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:ePkGbNQBHHA.1220@.TK2MSFTNGP04.phx.gbl...
> My database is no longer being published for replication because I used
> exec sp_replicationdboption 'databasename','merge publish',false
> Nonetheless, I added a dbo.sysmergesubscriptions table to this database
> and ran
> use master
> exec sp_dropdistributor @.no_checks = 1
> go
> but I still receive the error :
> Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
> Invalid object name 'dbo.sysmergesubscriptions'.
> Could it be because I need to add dbo.sysmergesubscriptions as a system
> object ? How do I do this ?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eZifFJQBHHA.2304@.TK2MSFTNGP02.phx.gbl...
>