Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts

Tuesday, March 27, 2012

Cannot drop the table transactional replication error 3724

This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?

--

I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.

In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:

Command attempted:
drop Table "dbo"."MyReplTable"

(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)

Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)

I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.

I presume that the sp_addarticle option @.pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)

Perhaps the sp_addpublication @.repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.

If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).

/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @.distributor = N'MyInstance', @.password = N''
GO
exec sp_adddistributiondb @.database = N'distribution', @.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.data_file_size = 4, @.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.log_file_size = 2, @.min_distretention = 0, @.max_distretention = 72, @.history_retention = 48, @.security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @.publisher = N'MyInstance', @.distribution_db = N'distribution', @.security_mode = 1, @.working_directory = N'C:\ThisFolder\MY2005DBWorking\repldata', @.trusted = N'false', @.thirdparty_flag = 0, @.publisher_type = N'MSSQLSERVER'
GO

use [DatabaseA]
exec sp_replicationdboption @.dbname = N'DatabaseA', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @.publication = N'PubAtoB', @.description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @.publication = N'PubAtoB', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1


use [DatabaseA]
exec sp_addarticle @.publication = N'PubAtoB', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseA]
exec sp_addsubscription @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseB', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseB', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--


use [DatabaseB]
exec sp_replicationdboption @.dbname = N'DatabaseB', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @.publication = N'PubBtoC', @.description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @.publication = N'PubBtoC', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1


use [DatabaseB]
exec sp_addarticle @.publication = N'PubBtoC', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseB]
exec sp_addsubscription @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseC', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseC', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--


May I ask why you need a republisher scenario, and not just have C subscriber to A?|||

The reason I ask this is because transactional replication is not designed for a republishing scenario. It can be done, but it involves a lot of hacks and so forth. Merge replication is typically used, and designed, for republishing scenario.

Maybe I should ask what your business needs are, and then we can discuss a solution.

|||

This is the nature of the system I've inherited. I'll represent the A,B,C level as servers because in reality, they often are a server with a single database.

The process thinking is that multiple Level B servers on other servers, spread the processing burden of moving the data from A to C. The C servers generally are physically clustered in the same general geographic area as the B server from which they source their data. Realistically, the A server might be feeding data to multiple B servers which in turn feed multiple C servers.

I hope that clarifies it for you. You can see that my little test scenario is much simplified from the physical reality. Our existing SQL 2000 replication makes use of both transactional and merge replication in this environment. I've only had light experience with transactional replication and not with either merge or republishing.

|||

Hi,

From your description, I assume you saw the error message from distribution agent, when replicating from B to C. Because you set @.pre_creation_cmd = N'drop', distribution agent needs to drop the table at C because it can replicate other transactions. But for some reason, this table is marked published.

So my question is: have you use DatabaseC as publisher? If it is the case, this may be the reason.

Peng

|||

Peng,

For the moment, I'm back to working on my actual script (vs the sample one). I can tell you that I do not have DatabaseC as a publisher in my actual script. However, I will need to make it a publisher because there is one more replication layer that I did not mention. Practically every table will be transactionally replicated to another server (DatabaseD) to support another process.

This is the way we are actually doing things in production now, in SQL 2000, without error.

Paul

|||I've just discovered that SQL 2000 replication was probably interfering with my SQL 2005 replication, so I removed SQL 2000 from my workstation (except for Virtual PC) and will try to work on this again tomorrow to see if anything has changed.

Cannot drop the table transactional replication error 3724

This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?

--

I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.

In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:

Command attempted:
drop Table "dbo"."MyReplTable"

(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)

Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)

I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.

I presume that the sp_addarticle option @.pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)

Perhaps the sp_addpublication @.repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.

If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).

/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @.distributor = N'MyInstance', @.password = N''
GO
exec sp_adddistributiondb @.database = N'distribution', @.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.data_file_size = 4, @.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.log_file_size = 2, @.min_distretention = 0, @.max_distretention = 72, @.history_retention = 48, @.security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @.publisher = N'MyInstance', @.distribution_db = N'distribution', @.security_mode = 1, @.working_directory = N'C:\ThisFolder\MY2005DBWorking\repldata', @.trusted = N'false', @.thirdparty_flag = 0, @.publisher_type = N'MSSQLSERVER'
GO

use [DatabaseA]
exec sp_replicationdboption @.dbname = N'DatabaseA', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @.publication = N'PubAtoB', @.description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @.publication = N'PubAtoB', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1


use [DatabaseA]
exec sp_addarticle @.publication = N'PubAtoB', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseA]
exec sp_addsubscription @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseB', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseB', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--


use [DatabaseB]
exec sp_replicationdboption @.dbname = N'DatabaseB', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @.publication = N'PubBtoC', @.description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @.publication = N'PubBtoC', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1


use [DatabaseB]
exec sp_addarticle @.publication = N'PubBtoC', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseB]
exec sp_addsubscription @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseC', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseC', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--


May I ask why you need a republisher scenario, and not just have C subscriber to A?|||

The reason I ask this is because transactional replication is not designed for a republishing scenario. It can be done, but it involves a lot of hacks and so forth. Merge replication is typically used, and designed, for republishing scenario.

Maybe I should ask what your business needs are, and then we can discuss a solution.

|||

This is the nature of the system I've inherited. I'll represent the A,B,C level as servers because in reality, they often are a server with a single database.

The process thinking is that multiple Level B servers on other servers, spread the processing burden of moving the data from A to C. The C servers generally are physically clustered in the same general geographic area as the B server from which they source their data. Realistically, the A server might be feeding data to multiple B servers which in turn feed multiple C servers.

I hope that clarifies it for you. You can see that my little test scenario is much simplified from the physical reality. Our existing SQL 2000 replication makes use of both transactional and merge replication in this environment. I've only had light experience with transactional replication and not with either merge or republishing.

|||

Hi,

From your description, I assume you saw the error message from distribution agent, when replicating from B to C. Because you set @.pre_creation_cmd = N'drop', distribution agent needs to drop the table at C because it can replicate other transactions. But for some reason, this table is marked published.

So my question is: have you use DatabaseC as publisher? If it is the case, this may be the reason.

Peng

|||

Peng,

For the moment, I'm back to working on my actual script (vs the sample one). I can tell you that I do not have DatabaseC as a publisher in my actual script. However, I will need to make it a publisher because there is one more replication layer that I did not mention. Practically every table will be transactionally replicated to another server (DatabaseD) to support another process.

This is the way we are actually doing things in production now, in SQL 2000, without error.

Paul

|||I've just discovered that SQL 2000 replication was probably interfering with my SQL 2005 replication, so I removed SQL 2000 from my workstation (except for Virtual PC) and will try to work on this again tomorrow to see if anything has changed.

Sunday, March 25, 2012

Cannot drop the table "xyz" as it is being used for replication

I have a replication setup running on 2 Win2K Server PCs with SQL
Server 2000.
Both the PCs have a common DB installed on them.
The setup takes care of starting one PC as primary and other as
backup.
It also starts the SQL replication (transactional), where both the PCs
are configured as publisher, distributor and subscriber.
Now the replication service creates a publication ONLY ON
PRIMARY and a subscription ONLY ON BACKUP. The replication starts.
When a switch-over of primary and backup is done, the existing
publication and subscription are deleted. A new publication is created
on new PRIMARY and a new subscription on the new BACKUP. Again,
replication works without a problem.
HOWEVER, sometimes, (I'm not able to reproduce & it is very
infrequent) on a switch-over I get thee error--"Cannot drop the table
"xyz" as it is being used for replication." The table is the one I
used for replication. Also, the primary keys exist on this table in
this case even though it's a part of subscription. The only solution
till now is to delete the entire replication on both machines and
re-create it.
I'm not using this DB anywhere else for any other purpose. Any
clues, why am I getting this problem occasionaly?
Thanks in advance.
Nelabh,
it's sometimes the case that some replication metadata is left hanging
around in system tables. sp_removedbreplication usually fixes these issues,
or the more granular sp_MSunmarkreplinfo may also be of interest.
HTH,
Paul Ibison

Cannot drop the distribution database 'distribution' because it is currently in use.

All,
I use stored procedures from C#.net to configure and remove
replication.
Whenever, i remove replication i try to first check if any process for
distribution DB (using sp_who2) is remaining in the SQL process space
and kill it.
The possible processes, i knew of using the distribution DB are:
1. SQL .NET data client provider
2. MSSQL EM
3. SQL Query Analyzer
I also checked that no cleanup processes are using it.
But still i get the error "Cannot drop the distribution database
'distribution' because it is currently in use."
Can anyone help in finding out who else is using the Distribution DB?
Please reply asap!
Thanks in advance,
Lavanya
It could be the distribution cleanup agent, the log reader, the distribition
agent or the merge agent. For a user database you would make it single-user
with yourself as the only user then drop it: Alter database distribution set
single_user with rollback immediate; use tempdb; drop database distribution.
For the distributor I'd run sp_dropdistributor followed by
sp_dropdistributiondb rather than the "drop database".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Try this select * From sys.sysprocesses where dbid=db_id('distribution')
in sql 2005 in sql 2000, try this
select * From sysprocesses where dbid=db_id('distribution')
Hilary Cotter
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
"Lavanya" <lavanyah@.gmail.com> wrote in message
news:1168258285.713911.183170@.s80g2000cwa.googlegr oups.com...
> All,
> I use stored procedures from C#.net to configure and remove
> replication.
> Whenever, i remove replication i try to first check if any process for
> distribution DB (using sp_who2) is remaining in the SQL process space
> and kill it.
> The possible processes, i knew of using the distribution DB are:
> 1. SQL .NET data client provider
> 2. MSSQL EM
> 3. SQL Query Analyzer
> I also checked that no cleanup processes are using it.
> But still i get the error "Cannot drop the distribution database
> 'distribution' because it is currently in use."
> Can anyone help in finding out who else is using the Distribution DB?
> Please reply asap!
> Thanks in advance,
> Lavanya
>

Cannot drop database (hanging in loading and marked for replication)

Hi
I have restored a database to the same instance as it was backup, but as a
copy of the original database (I need a couple of Stored Procedures), but
while restoring the database is to hanging with (Loading).
I've tried to drop the database but it says that it cannot be dropped
because it is marked for replication, I've then tried to drop the
replication on the database but it can't, it says that the database cannot
be opened because it is in the middle of a restore.
What to do? I'm pretty stuck...
I've running SQL Server 2000 on a windows 2003 server SP1, the database is
less than 100MB in size.
-Kim
I was having the same problem restoring my database (without replication
issue) and it's stuck loading for an hour, so stoped SQL Server and removed
that datbase mdf and log files..
Then restarted my server and run mentanance task (optmizing & fix intgerity)
on master database..
Then restore my database from the backup
it finished in less than a minute.
"Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
news:43f6ec88$1@.news.carlbro.dk...
> Hi
> I have restored a database to the same instance as it was backup, but as a
> copy of the original database (I need a couple of Stored Procedures), but
> while restoring the database is to hanging with (Loading).
> I've tried to drop the database but it says that it cannot be dropped
> because it is marked for replication, I've then tried to drop the
> replication on the database but it can't, it says that the database cannot
> be opened because it is in the middle of a restore.
> What to do? I'm pretty stuck...
> I've running SQL Server 2000 on a windows 2003 server SP1, the database is
> less than 100MB in size.
> -Kim
>
|||Hi,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:ummPctJNGHA.532@.TK2MSFTNGP15.phx.gbl...
>I was having the same problem restoring my database (without replication
>issue) and it's stuck loading for an hour, so stoped SQL Server and removed
>that datbase mdf and log files..
> Then restarted my server and run mentanance task (optmizing & fix
> intgerity) on master database..
> Then restore my database from the backup
> it finished in less than a minute.
> "Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
> news:43f6ec88$1@.news.carlbro.dk...
>

Cannot drop database (hanging in loading and marked for replication)

Hi
I have restored a database to the same instance as it was backup, but as a
copy of the original database (I need a couple of Stored Procedures), but
while restoring the database is to hanging with (Loading).
I've tried to drop the database but it says that it cannot be dropped
because it is marked for replication, I've then tried to drop the
replication on the database but it can't, it says that the database cannot
be opened because it is in the middle of a restore.
What to do? I'm pretty stuck...
I've running SQL Server 2000 on a windows 2003 server SP1, the database is
less than 100MB in size.
-KimI was having the same problem restoring my database (without replication
issue) and it's stuck loading for an hour, so stoped SQL Server and removed
that datbase mdf and log files..
Then restarted my server and run mentanance task (optmizing & fix intgerity)
on master database..
Then restore my database from the backup
it finished in less than a minute.
"Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
news:43f6ec88$1@.news.carlbro.dk...
> Hi
> I have restored a database to the same instance as it was backup, but as a
> copy of the original database (I need a couple of Stored Procedures), but
> while restoring the database is to hanging with (Loading).
> I've tried to drop the database but it says that it cannot be dropped
> because it is marked for replication, I've then tried to drop the
> replication on the database but it can't, it says that the database cannot
> be opened because it is in the middle of a restore.
> What to do? I'm pretty stuck...
> I've running SQL Server 2000 on a windows 2003 server SP1, the database is
> less than 100MB in size.
> -Kim
>|||Hi,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:ummPctJNGHA.532@.TK2MSFTNGP15.phx.gbl...
>I was having the same problem restoring my database (without replication
>issue) and it's stuck loading for an hour, so stoped SQL Server and removed
>that datbase mdf and log files..
> Then restarted my server and run mentanance task (optmizing & fix
> intgerity) on master database..
> Then restore my database from the backup
> it finished in less than a minute.
> "Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
> news:43f6ec88$1@.news.carlbro.dk...
>|||"Islamegy":[vbcol=seagreen]
[vbcol=seagreen]
Hi again,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm|||(sorry if this message is posted more than once, I've had trouble submitting
my mail to my newsserver)
"Islamegy":[vbcol=seagreen]
[vbcol=seagreen]
Hi again,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm

Cannot drop database (hanging in loading and marked for replication)

Hi
I have restored a database to the same instance as it was backup, but as a
copy of the original database (I need a couple of Stored Procedures), but
while restoring the database is to hanging with (Loading).
I've tried to drop the database but it says that it cannot be dropped
because it is marked for replication, I've then tried to drop the
replication on the database but it can't, it says that the database cannot
be opened because it is in the middle of a restore.
What to do? I'm pretty stuck...
I've running SQL Server 2000 on a windows 2003 server SP1, the database is
less than 100MB in size.
-KimI was having the same problem restoring my database (without replication
issue) and it's stuck loading for an hour, so stoped SQL Server and removed
that datbase mdf and log files..
Then restarted my server and run mentanance task (optmizing & fix intgerity)
on master database..
Then restore my database from the backup
it finished in less than a minute.
"Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
news:43f6ec88$1@.news.carlbro.dk...
> Hi
> I have restored a database to the same instance as it was backup, but as a
> copy of the original database (I need a couple of Stored Procedures), but
> while restoring the database is to hanging with (Loading).
> I've tried to drop the database but it says that it cannot be dropped
> because it is marked for replication, I've then tried to drop the
> replication on the database but it can't, it says that the database cannot
> be opened because it is in the middle of a restore.
> What to do? I'm pretty stuck...
> I've running SQL Server 2000 on a windows 2003 server SP1, the database is
> less than 100MB in size.
> -Kim
>|||Hi,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm
"Islamegy®" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:ummPctJNGHA.532@.TK2MSFTNGP15.phx.gbl...
>I was having the same problem restoring my database (without replication
>issue) and it's stuck loading for an hour, so stoped SQL Server and removed
>that datbase mdf and log files..
> Then restarted my server and run mentanance task (optmizing & fix
> intgerity) on master database..
> Then restore my database from the backup
> it finished in less than a minute.
> "Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
> news:43f6ec88$1@.news.carlbro.dk...
>> Hi
>> I have restored a database to the same instance as it was backup, but as
>> a copy of the original database (I need a couple of Stored Procedures),
>> but while restoring the database is to hanging with (Loading).
>> I've tried to drop the database but it says that it cannot be dropped
>> because it is marked for replication, I've then tried to drop the
>> replication on the database but it can't, it says that the database
>> cannot be opened because it is in the middle of a restore.
>> What to do? I'm pretty stuck...
>> I've running SQL Server 2000 on a windows 2003 server SP1, the database
>> is less than 100MB in size.
>> -Kim
>|||"Islamegy®":
>>I was having the same problem restoring my database (without replication
>>issue) and it's stuck loading for an hour, so stoped SQL Server and
>>removed that datbase mdf and log files..
>> Then restarted my server and run mentanance task (optmizing & fix
>> intgerity) on master database..
>> Then restore my database from the backup
>> it finished in less than a minute.
>> "Kim":
>> I have restored a database to the same instance as it was backup, but as
>> a copy of the original database (I need a couple of Stored Procedures),
>> but while restoring the database is to hanging with (Loading).
>> I've tried to drop the database but it says that it cannot be dropped
>> because it is marked for replication, I've then tried to drop the
>> replication on the database but it can't, it says that the database
>> cannot be opened because it is in the middle of a restore.
>> What to do? I'm pretty stuck...
>> I've running SQL Server 2000 on a windows 2003 server SP1, the database
>> is less than 100MB in size.
>> -Kim
Hi again,
I've removed the psysical files as suggested and runned the maintenance task
"check database integrity" on master (couldn't find optimize + fix
integrity).
This hasn't removed the entry from the enterprise manager... should I make
a new restore and then remove the entry ? (I don't need to restore it
anymore)
-KIm

Cannot drop article ''TABLE60'' from publication ''DB_Comp''...

[ Merge Replication ]
I work with a software that the act deletion and creation of the table
and deletion and creation and change of the field by the users happens
so much.
for deletion of the table using this command :
sp_dropmergearticle @.publication='DB_Doc',
@.article = 'TABLE65',
@.force_invalidate_snapshot=1
, but after execute this command displayed below message :
"Cannot drop article 'TABLE65' from publication 'DB_Doc' because its
snapshot has been run and this publication could have active
subscriptions."
how can i drop the table in active merge replication with T-SQL(System
Stored Procedure)?
thanks.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
As far as I know, you can't. The best you can do is synchronize all
subscribers until no change is applied, drop the publication, change the
table on the publisher and subscribers, readd the publication and do a
nosync initialization.
Rgds,
Paul Ibison

Cannot detach the database for previous publications

I had replication job in one database. Since I need to detach the database
and move it somewhere else, I deleted all publications. However when I tried
to detach the db, the error message says that the database cannot be detached
because it is replicated. I updated the statistics of both that database and
distribution database. However the error remains. Please advice.
Thanks,
Charts
Try using sp_dboption to set 'published' and 'merge publish' to false.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Great ! It works. Thanks so much.
"Paul Ibison" wrote:

> Try using sp_dboption to set 'published' and 'merge publish' to false.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Cannot detach suspect db

A db is flagged as suspect. I tried to detach it or take
offline but was unable to as the db is replicated (merge
replication). At the same time I was unable to cancel the
replication because the db wasn't found.
The suspect status was caused by the db failing to close
cleanly after a failed back-up. I also haven't been able
to succesfully run sp_resetstatus.
Any ideas onhow to break out of this catch 22 situation?
RegardsHere's an old post i found that explains how to fix a suspect db :-
http://groups.google.co.uk/groups?q=sql+2000+suspect+fix&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=dcde2a5a.0309151952.424b43cb%40posting.google.com&rnum=1
--
HTH
Ryan Waight, MCDBA, MCSE
<japan11@.hotmail.com> wrote in message
news:074901c39147$f819ece0$a301280a@.phx.gbl...
> A db is flagged as suspect. I tried to detach it or take
> offline but was unable to as the db is replicated (merge
> replication). At the same time I was unable to cancel the
> replication because the db wasn't found.
> The suspect status was caused by the db failing to close
> cleanly after a failed back-up. I also haven't been able
> to succesfully run sp_resetstatus.
> Any ideas onhow to break out of this catch 22 situation?
> Regards

Thursday, March 22, 2012

cannot deliver sp_repldropcolum schema change

Hello
As our customers use our product often offline, we implemted a sql server
2000 merge replication with msde anonymous subscribers.
With the new product release go some schemachanges, which we implemted with
sp_repladdcolumn and sp_repldropcolumn (e.g." exec sp_repldropcolumn
@.source_object = 'verlauf_ver', @.column = 'c_mandant',
@.force_reinit_subscription = 1").
I can complete the changes on the publisher without errors (only the
warning, that you can synchronize with sql 2000 and above only).
When I synchronize the subscriber, firstly it seems to work, but the it
stops and says, it cannot deliver the changes "exec sp_repldropcolumn..."
Are there any ideas?
Is it possible, there's a kind of restriction on the amount of changes?
Thanks for any help
Regards
Please can you post up the exact error message. Also you might want to
enable logging and see if that gives any more info (and post that up as well
please).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hello Paul
At the moment, I'm checking out something else.
But after that, I'll do what you ask me for. But can you tell me, how I can
enable Logging? And where to find this Log?
Thanks a lot
Aline
"Paul Ibison" wrote:

> Please can you post up the exact error message. Also you might want to
> enable logging and see if that gives any more info (and post that up as well
> please).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Hi Aline - this article explains it well:
http://support.microsoft.com/?id=312292
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hello Paul
Thanks for the Link, which was very useful. I didn't realise, I could enable
such a logging (though I've already read about it before)
So here's the statement:
exec sp_repldropcolumn @.source_object = 'c_controldef', @.column = 'mandid',
@.force_invalidate_snapshot = 1, @.force_reinit_subscription = 1
and here's the log (in German, sorry):
Microsoft SQL Server-Merge-Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Percent Complete: 0
Verbindung mit Abonnent 'winxpsp2\syseca'
Connecting to Abonnent 'winxpsp2\syseca.challenge'
Server: winxpsp2\syseca
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: rai_application
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[16.11.2006 13:55:53]winxpsp2\syseca.challenge: {call sp_MSgetversion }
[16.11.2006 13:55:53]winxpsp2\syseca.challenge: {?=call
sp_helpsubscription_properties (N'vistatestpc\syseca', N'challenge',
N'challenge')}
Distributor security mode: 0, login name: rai_application, password: ********.
Percent Complete: 0
Verbindung mit Verteiler 'vistatestpc\syseca'
Connecting to Verteiler 'vistatestpc\syseca.'
Server: vistatestpc\syseca
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: guest
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[16.11.2006 13:55:53]vistatestpc\syseca.: {call sp_MSgetversion }
[16.11.2006 13:55:53]vistatestpc\syseca.: {call sp_helpdistpublisher
(N'vistatestpc\syseca') }
[16.11.2006 13:55:53]vistatestpc\syseca.distribution: select datasource,
srvid from master..sysservers where upper(srvname) =
upper(N'vistatestpc\syseca')
[16.11.2006 13:55:53]vistatestpc\syseca.distribution: {call
sp_MSadd_merge_anonymous_agent(N'0', N'challenge', N'challenge',
N'challenge', N'winxpsp2\syseca',?, 667)}
[16.11.2006 13:55:53]vistatestpc\syseca.distribution: {call
sp_MShelp_profile (2, 4, N'')}
Percent Complete: 0
Connecting to Verleger 'vistatestpc\syseca.challenge'
Initialisiert
Percent Complete: 1
Verbindung mit Verleger 'vistatestpc\syseca'
Server: vistatestpc\syseca
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: rai_application
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[16.11.2006 13:55:53]vistatestpc\syseca.challenge: set nocount on declare
@.dbname sysname select @.dbname = db_name() declare @.collation nvarchar(255)
select @.collation = convert(nvarchar(255), databasepropertyex(@.dbname,
N'COLLATION')) select collationproperty(@.collation, N'CODEPAGE') as
'CodePage', collationproperty(@.collation, N'LCID') as 'LCID',
collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
Connecting to Verleger 'vistatestpc\syseca.challenge'
Server: vistatestpc\syseca
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: rai_application
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[16.11.2006 13:55:54]vistatestpc\syseca.challenge: {call sp_MSgetversion }
Percent Complete: 3
Ruft Publikationsinformationen ab
Percent Complete: 4
Ruft Abonnementinformationen ab
Connecting to Abonnent 'winxpsp2\syseca.challenge'
Server: winxpsp2\syseca
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: rai_application
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
Server: winxpsp2\syseca
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: rai_application
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[16.11.2006 13:55:54]vistatestpc\syseca.challenge: exec sp_datatype_info 0, 3
Percent Complete: 4
[16.11.2006 13:55:54]winxpsp2\syseca.challenge: exec sp_repldropcolumn
'[dbo].[c_controldef]', 'mandid', 1
Eine Spalte wurde zur replizierten Tabelle hinzugefügt oder aus dieser
gel?scht.
Das Schemaskript 'exec sp_repldropcolumn '[dbo].[c_controldef]', 'mandid',
1' konnte nicht an den Abonnenten weitergegeben werden.
Percent Complete: 0
Das Schemaskript 'exec sp_repldropcolumn '[dbo].[c_controldef]', 'mandid',
1' konnte nicht an den Abonnenten weitergegeben werden.
Percent Complete: 0
Category:NULL
Source: Mergereplikationsprovider
Number: -2147201001
Message: Das Schemaskript 'exec sp_repldropcolumn '[dbo].[c_controldef]',
'mandid', 1' konnte nicht an den Abonnenten weitergegeben werden.
at the end it says: The schemascript sp_repldropcolumn
'[dbo].[c_controldef]', 'mandid', 1' couldn't be delivered to the subscriber
Unfortunately it doesn't tell my why.
There are a lot of different changes before which work fine. And afterwards
are also 46 sp_repldropcolumn.
Strangely, if I comment out these 47 sp_repldropcolumn's, update the
publisher, synchronize the subscriber, and then update the publisher with
these 47 47 sp_repldropcolumn's and synch the subscriber, it works...
As the product is install at more than 100 customers, I need something
automatic.
Any further help?
Thanks
Aline
|||My guess is that something references the column on the subscriber. Please
take a look at this article and see if it applies:
http://support.microsoft.com/kb/816755
One way to test this would be to restore a backup of the subscriber database
to another database nema and try to drop the column directly and hopefully
you wont be able to and will get a message referring to the constraint that
prevents the drop. If you can get this, you can drop the constraint using
sp_addscriptexec (assuming it is nothing to do with a filter).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul
It still doesn't work correctly. I've got the following error, when adding a
script:
Script:
....
exec sp_addscriptexec @.publication = N'challenge',
@.scriptfile='c:\programme\syseca\DB-Scripts\Replikation\ReplicationScripts\DropConstra ints.sql'
exec sp_repldropcolumn @.source_object = 'c_controldef', @.column = 'mandid',
@.force_invalidate_snapshot = 1, @.force_reinit_subscription = 1
...
error:
xpsql.cpp: Fehler 997 von GetProxyAccount in Zeile 604
Meldung 21330, Ebene 16, Status 1, Server VISTATESTPC\SYSECA, Prozedur
sp_MScopyscriptfile, Zeile 31
Fehler beim Erstellen eines Unterverzeichnisses unter dem
Replikationsarbeitsverzeichnis. (md
"\\vistatestpc\snapshot\20061116210031497")
--> error while building subdirectory...
I think, the path should be something like this:
"\\vistatestpc\snapshot\unc\SERVERNAME$INSTANCENAM E_PUBLICATION_DATABASE\20061116210031497"
Shouldn't the distributor handle this? By the way: Distributor and Publisher
are installed on the same server and dbms.
what can I do?
Thanks a lot
Aline
|||I'm not familiar with the normal log messages, but it looks like you have
configured a proxy account. Is the proxy account that the merge agent is
using in the Local admins group on the server? (or at least set up with the
necessary rights)? (it's generally much easier to have the merge agent run
as the sql server agent although SQL Serever 2005 uses these proxies by
default) Also, is there enough space on the server?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||sql server agent runs as a local administrator. so I think it has all
necessary rights.
what confuses me is, why does it try to make a new folder and why are these
typical "unc\servername..." missing in the path?
|||I'll take a proper look tomorrow when I get back to work if nobody's solved
it by then.
Before that, can you confirm is you are using a proxy account for
non-sysadmins or not, and that the account (if you are) has the necessary
rights.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

cannot deliver inserts

HI we are using merge replication and now i am getting the
error message as ' cannot deliver inserts to the publisher'
Kiran,
does restarting the agent fix the issue? If not, then are you replicating to
CE - known issue there
(http://support.microsoft.com/default...b;en-us;328314). If neither
of these apply, then please enable logging
(http://support.microsoft.com/?id=312292) and post up further details of the
schema and error message>
Thanks,
Paul Ibison
|||HI paul thanks,
but we are using sql2000 merge replication.
so this article deosn't apply to us.

>--Original Message--
>Kiran,
>does restarting the agent fix the issue? If not, then are
you replicating to
>CE - known issue there
>(http://support.microsoft.com/default.aspx?scid=kb;en-
us;328314). If neither
>of these apply, then please enable logging
>(http://support.microsoft.com/?id=312292) and post up
further details of the
>schema and error message>
>Thanks,
>Paul Ibison
>
>.
>
|||Kiran,
ok, then check that the subscriber can 'see' the publisher using QA. If not,
then it's a connectivity issue, if it can then enable logging and post up
the message.
Regards,
Paul Ibison
|||Hi paul,
it was happening becos of the conflicts we had for that
subscriber. It was taking so long to reconcoile the
conflicts. i ended up recreating subscription and now it
seems fine.
thanks
>--Original Message--
>Kiran,
>ok, then check that the subscriber can 'see' the
publisher using QA. If not,
>then it's a connectivity issue, if it can then enable
logging and post up
>the message.
>Regards,
>Paul Ibison
>
>.
>
|||Kiran,
thanks for the update. Recreating the subscription has removed the conflicts
so you don't experience the same problem straight away. To prevent this
error in future, you might want to create a custom profile for your merge
agent and increase the QueryTimeOut .
Regards,
Paul Ibison

cannot delete subscriptions

To test SQL2000 replication, I enabled replications for NorthWind between
ServerA and ServerB. ServerA was the publisher and distributor, ServerB was
the subscriber.
After test, I deleted everything about replication on ServerA, at last, I
disabled replication. However, when I ckeck ServerB, in the
Replication\Subscriptions container, There is a "ServerA:NorthWind:" object.
I cannot remove it. If I right click this item, it only has a "set update
method..." menu which is grayed out, and another "help" menu. It looks like
a phantom object.
I then re-configure the server as a subscriber again (subscribe test DB and
set ServerA as a publisher), the Replication\Subscriptions container now has
another object "ServerA:test:test", and the content menu looks normal, which
includes "New pull subscription...", "Reinitialize", "delete"...etc. menu.
How can I delete the "ServerA:NorthWind:" object?
OK, it looks like you have lingering metadata. If you can, can you disable
replication one more time.
then run the following script
create table whack_me ([database] sysname, [table] sysname, constraint
Whack_me_pk primary key([database], [table]))
GO
insert into whack_me([database],[table])
exec sp_msforeachdb 'select [database]=''?'',[table]=''
MSreplication_subscriptions'' from ?.dbo.MSreplication_subscriptions where
publisher=@.@.servername'
insert into whack_me([database],[table])
exec sp_msforeachdb 'select [database]=''?'',[table]=''
MSsubscription_agents'' from ?.dbo.MSsubscription_agents where
publisher=@.@.servername'
whack_me will have a list of the lingering subscriptions and the databases
they are in. Delete the contents of these tables where the publisher
=@.@.servername
Hilary Cotter
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
"MLi" <lige888@.gmail.com> wrote in message
news:uxGiwzXEGHA.3004@.TK2MSFTNGP15.phx.gbl...
> To test SQL2000 replication, I enabled replications for NorthWind between
> ServerA and ServerB. ServerA was the publisher and distributor, ServerB
> was the subscriber.
> After test, I deleted everything about replication on ServerA, at last, I
> disabled replication. However, when I ckeck ServerB, in the
> Replication\Subscriptions container, There is a "ServerA:NorthWind:"
> object. I cannot remove it. If I right click this item, it only has a "set
> update method..." menu which is grayed out, and another "help" menu. It
> looks like a phantom object.
> I then re-configure the server as a subscriber again (subscribe test DB
> and set ServerA as a publisher), the Replication\Subscriptions container
> now has another object "ServerA:test:test", and the content menu looks
> normal, which includes "New pull subscription...", "Reinitialize",
> "delete"...etc. menu.
> How can I delete the "ServerA:NorthWind:" object?
>
|||I ran the script on ServerA and got a bunch of errors:
Invalid object name 'Northwind.dbo.MSsubscription_agents'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'master.dbo.MSsubscription_agents'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'model.dbo.MSsubscription_agents'.
Msg 208, Level 16, State 1, Line 1
....
I just want to delete everything about NorthWind replication, any easy way?
like delete all rows about NorthWind in some particular table?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Oxgn9BaEGHA.216@.TK2MSFTNGP15.phx.gbl...
> OK, it looks like you have lingering metadata. If you can, can you disable
> replication one more time.
> then run the following script
> create table whack_me ([database] sysname, [table] sysname, constraint
> Whack_me_pk primary key([database], [table]))
> GO
> insert into whack_me([database],[table])
> exec sp_msforeachdb 'select [database]=''?'',[table]=''
> MSreplication_subscriptions'' from ?.dbo.MSreplication_subscriptions
> where publisher=@.@.servername'
> insert into whack_me([database],[table])
> exec sp_msforeachdb 'select [database]=''?'',[table]=''
> MSsubscription_agents'' from ?.dbo.MSsubscription_agents where
> publisher=@.@.servername'
> whack_me will have a list of the lingering subscriptions and the databases
> they are in. Delete the contents of these tables where the publisher
> =@.@.servername
> --
> Hilary Cotter
> 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
> "MLi" <lige888@.gmail.com> wrote in message
> news:uxGiwzXEGHA.3004@.TK2MSFTNGP15.phx.gbl...
>
|||in the table whack_me you will find a list of databases and tables. Go
through these databases and tables and delete the contents of them.
Ignore the error messages.
Hilary Cotter
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
"MLi" <lige888@.gmail.com> wrote in message
news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>I ran the script on ServerA and got a bunch of errors:
> Invalid object name 'Northwind.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'master.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'model.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> ...
> I just want to delete everything about NorthWind replication, any easy
> way? like delete all rows about NorthWind in some particular table?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Oxgn9BaEGHA.216@.TK2MSFTNGP15.phx.gbl...
>
|||sp_removedbreplication
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"MLi" <lige888@.gmail.com> wrote in message
news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>I ran the script on ServerA and got a bunch of errors:
> Invalid object name 'Northwind.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'master.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'model.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> ...
> I just want to delete everything about NorthWind replication, any easy
> way? like delete all rows about NorthWind in some particular table?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Oxgn9BaEGHA.216@.TK2MSFTNGP15.phx.gbl...
>
|||With all due respect Mike the OP did this to begin with.
Hilary Cotter
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
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ecQUaGpEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> sp_removedbreplication
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "MLi" <lige888@.gmail.com> wrote in message
> news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>
|||I'm sorry Hilary, I guess I was just too stupid to realize that. Thank you
for helping me understand my stupidity.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ulLILIsEGHA.3708@.TK2MSFTNGP10.phx.gbl...
> With all due respect Mike the OP did this to begin with.
> --
> Hilary Cotter
> 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
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:ecQUaGpEGHA.1312@.TK2MSFTNGP09.phx.gbl...
>
|||Just found another way to delete this lingering object-- by SQL 2005
Management Studio
The object has a "delete" context menu
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ecQUaGpEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> sp_removedbreplication
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "MLi" <lige888@.gmail.com> wrote in message
> news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>

Tuesday, March 20, 2012

Cannot create subscriber on MSDE2000

I am very new at SQL replication. I set up a publication and distributor at
our SQL 2000 server. All went fine. Now I am trying to create a laptop
subscriber to use (I think) an anonymous subscription. I setup the laptop
with MSDE 2000 SP3. I now want to create a subscription while connected to
the network. I want to use the Windows Syncronizer to synch the database.
I cannot find out how to create the initial subscription database on the
laptop. When I try to manually configure the Windows Synchronizer, I get an
error "The process could not access database 'MCFIData' on server 'LAPTOP1'.
Database is invalid or cannot be accessed"
Well DUH...I was told that it would be created if it does not exist. Any
help would be appreciated. Thanks.
David
Ignore this. I did not have the SQL Agent running on the laptop with MSDE
2000 SP3. I'm not sure why, but there was no prompt for this option when I
installed MSDE. Also there was no instruction to do so on the documentation
for setting up MSDE to be a subsriber!
David
"David" <dlchase@.lifetimeinc.com> wrote in message
news:elqk$rE%23FHA.1248@.TK2MSFTNGP14.phx.gbl...
>I am very new at SQL replication. I set up a publication and distributor
>at our SQL 2000 server. All went fine. Now I am trying to create a laptop
>subscriber to use (I think) an anonymous subscription. I setup the laptop
>with MSDE 2000 SP3. I now want to create a subscription while connected to
>the network. I want to use the Windows Syncronizer to synch the database.
>I cannot find out how to create the initial subscription database on the
>laptop. When I try to manually configure the Windows Synchronizer, I get
>an error "The process could not access database 'MCFIData' on server
>'LAPTOP1'. Database is invalid or cannot be accessed"
> Well DUH...I was told that it would be created if it does not exist. Any
> help would be appreciated. Thanks.
> David
>

Cannot create publication after SP1 upgrade

After upgrading SQL Server 2005 with the recent service pack, I am unable to create a publication. When I right-click on [INSTANCE]/Replication/Local Publications and select "New Publication...," the mouse cursor turns into an hourglass, but the wizard never shows up, nor is any error displayed.

There is a logged error that's been showing up that may or may not be related. The error message is "Replication-(null): agent (null) scheduled for retry. Could not clean up the distribution history tables."

Any ideas on how we can restore replication functionality on our SQL Server instance?

Can you run profiler before invoking New Publication wizard to trace which statement is causing the problem? What kind of publications do you have before upgrade to SP1? Is distributor on the same machine as publisher?

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||I ran the profiler, and I'm getting some strange output. Apparently, the command "select * from sysdatabases where name=N'distribution'" is being run over and over again. Strangely enough, there is a row returned from that command that points to where our distribution database was located, but the database isn't shown under the system databases in Management Studio.

Before upgrading to SP1, we were using merge publications with the distributor on the same machine as the publisher.

|||

Can you send sp1 upgrade log files and profiler trace to me? I will send you a separate email.

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

From the sp1 upgrade log file, looks like you have run SP1 upgrade twice, the first time it runs, SQLDEV01 machine is treated as a distributor and distribution database is upgrade successfully(from what the log file says). The 2nd time the sp1 upgrade runs, it didn't treat this machine as distributor and didn't upgrade distribution database (also you mentioned distribution database is no longer marked as system database, which is suspicious, we need to figure out why). Did you notice any error during sp1 upgrade? Why you run sp1 upgrade twice? Did you drop distributor or change replication settings after first sp1 upgrade?

Please also email a profiler trace for the New publication wizard so we can further diagnose the issue. When using profiler, make sure select following options (and unselect any options not mentioned below):

Error and Warnings --> ErrorLog, Exceptions, User Error Message

Stored Procedures --> RPC Starting, RPC Completed, SP Starting, SP Completed, SP Stmt Starting

TSQL --> SQL: Batch Starting, SQL: Batch Completed, SQL: StmtStarting

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||It's finally working! I suspect that all the problems stemmed from there being an inconsistency in the sysdatabases table. I ran "DROP DATABASE distributor" under the master database, and afterward, I was able to set up the distributor, publishers, and subscribers without any problems.

Thank you, Zhiqiang Feng, for your email assistance.

Monday, March 19, 2012

Cannot create Merge Replication in 2005

Hi to All,
I had sql 2005 with sp3 and i was working succesfully with merge
replication on Sql ce.
Yestarday i upgrade to sql 2005 (first upgrate my sql200 with sp4)
Now i am here in sql 2005 trying to create a new merge replication on
windows mobile 2005 and i get to the final step this error
------
TITLE: New Publication Wizard
New Publication Wizard encountered one or more errors while retrieving
publication names.
For help, click:
http://go.microsoft.com/fwlink?ProdN...s&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid column name 'snapshot_jobid'.
Invalid column name 'use_partition_groups'.
Invalid column name 'replicate_ddl'.
Invalid column name 'publication_number'.
Invalid column name 'allow_subscriber_initiated_snapshot'.
Invalid column name 'allow_web_synchronization'.
Invalid column name 'web_synchronization_url'.
Invalid column name 'allow_partition_realignment'.
Invalid column name 'retention_period_unit'.
Invalid column name 'decentralized_conflicts'.
Invalid column name 'generation_leveling_threshold'.
Invalid column name 'automatic_reinitialization_policy'.
Invalid column name 'snapshot_jobid'.
Invalid column name 'use_partition_groups'.
Invalid column name 'replicate_ddl'.
Invalid column name 'publication_number'.
Invalid column name 'allow_subscriber_initiated_snapshot'.
Invalid column name 'allow_web_synchronization'.
Invalid column name 'web_synchronization_url'.
Invalid column name 'allow_partition_realignment'.
Invalid column name 'retention_period_unit'.
Invalid column name 'decentralized_conflicts'.
Invalid column name 'generation_leveling_threshold'.
Invalid column name 'automatic_reinitialization_policy'. (Microsoft SQL
Server, Error: 207)
For help, click:
http://go.microsoft.com/fwlink?ProdN...7&LinkId=20476
------
Can anyone please help ?
Thanks
Savvas
What all did you do when you upgraded?
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1139562228.104138.260770@.f14g2000cwb.googlegr oups.com...
> Hi to All,
> I had sql 2005 with sp3 and i was working succesfully with merge
> replication on Sql ce.
> Yestarday i upgrade to sql 2005 (first upgrate my sql200 with sp4)
> Now i am here in sql 2005 trying to create a new merge replication on
> windows mobile 2005 and i get to the final step this error
> ------
> TITLE: New Publication Wizard
> --
> New Publication Wizard encountered one or more errors while retrieving
> publication names.
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...s&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> Invalid column name 'snapshot_jobid'.
> Invalid column name 'use_partition_groups'.
> Invalid column name 'replicate_ddl'.
> Invalid column name 'publication_number'.
> Invalid column name 'allow_subscriber_initiated_snapshot'.
> Invalid column name 'allow_web_synchronization'.
> Invalid column name 'web_synchronization_url'.
> Invalid column name 'allow_partition_realignment'.
> Invalid column name 'retention_period_unit'.
> Invalid column name 'decentralized_conflicts'.
> Invalid column name 'generation_leveling_threshold'.
> Invalid column name 'automatic_reinitialization_policy'.
> Invalid column name 'snapshot_jobid'.
> Invalid column name 'use_partition_groups'.
> Invalid column name 'replicate_ddl'.
> Invalid column name 'publication_number'.
> Invalid column name 'allow_subscriber_initiated_snapshot'.
> Invalid column name 'allow_web_synchronization'.
> Invalid column name 'web_synchronization_url'.
> Invalid column name 'allow_partition_realignment'.
> Invalid column name 'retention_period_unit'.
> Invalid column name 'decentralized_conflicts'.
> Invalid column name 'generation_leveling_threshold'.
> Invalid column name 'automatic_reinitialization_policy'. (Microsoft SQL
> Server, Error: 207)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...7&LinkId=20476
> ------
> Can anyone please help ?
> Thanks
> Savvas
>

Cannot create merge replication from SQLSERVER 2000 to ORACLE

Hello everybody,
I am trying to build a merge replication between 2 servers
i managed to create it between to SQLSERVER 2000 with no problem
but now i am trying to do it between SQLSERVER 2000 and ORACLE server
when i try to do a transactional replication it work fine between the two
servers
but when i try to do the merge replication the following error occur:
The step did not generate any output. The step failed
i searched the topics and did not find any help but i managed to try writing
the output error on a file and it gave me the following message:
Microsoft SQL Server Merge Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: CARLO-testrep-testrep-OLINK-1
Category:NULL
Source: Merge Process
Number: -2147200933
Message: The process could not load the Merge Replication Provider for
'Oracle'. Check to see if the component is registered correctly.
if anyone can help me with, please do
best Regards
Nicolas
This is not supported, you can only replicate to SQL Server, MSAcess and SQL
CE. You will have to cobble a solution together for this.
Hilary Cotter
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
"Nicolas Akl" <c_khano@.hotmail.com> wrote in message
news:D66DB88D-29E0-449B-B578-D775BFDD2572@.microsoft.com...
> Hello everybody,
> I am trying to build a merge replication between 2 servers
> i managed to create it between to SQLSERVER 2000 with no problem
> but now i am trying to do it between SQLSERVER 2000 and ORACLE server
> when i try to do a transactional replication it work fine between the two
> servers
> but when i try to do the merge replication the following error occur:
> The step did not generate any output. The step failed
> i searched the topics and did not find any help but i managed to try
writing
> the output error on a file and it gave me the following message:
> Microsoft SQL Server Merge Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: CARLO-testrep-testrep-OLINK-1
> Category:NULL
> Source: Merge Process
> Number: -2147200933
> Message: The process could not load the Merge Replication Provider for
> 'Oracle'. Check to see if the component is registered correctly.
> if anyone can help me with, please do
> best Regards
> Nicolas
|||Dear Hilary,
As i understand from you, that there is no way to make a merge
replication from sqlserver 2000 to Oracle,
if so why does it support transactional replication and not the merge
replication
Best Regards
Carlo khanati.

Sunday, March 11, 2012

Cannot create article

What are some things that would stop an article from being created? I am
running a helpfully simple Snapshot Replication on my pc. I am configuring
the SQL Server to do all three roles: Publisher, Distributor and Subscriber.
I am only replicating the Authors table from the Pubs database to a database
called Pubs_Copy.
The error messages is as follows:
SQL Server Enterprise Manager could not create article 'authors' based on
the object 'authors' Error 50007: xp_execresultset: unable to get a bound
connection back to server.
What can check?
You have a remote distributor which you do not have the correct
administrative password for.
Hilary Cotter
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
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:EE5015FD-8630-409A-BC9C-EF91E0480C8A@.microsoft.com...
> What are some things that would stop an article from being created? I am
> running a helpfully simple Snapshot Replication on my pc. I am
configuring
> the SQL Server to do all three roles: Publisher, Distributor and
Subscriber.
> I am only replicating the Authors table from the Pubs database to a
database
> called Pubs_Copy.
> The error messages is as follows:
> SQL Server Enterprise Manager could not create article 'authors' based on
> the object 'authors' Error 50007: xp_execresultset: unable to get a bound
> connection back to server.
> What can check?

Saturday, February 25, 2012

Cannot connect to to the other server when setting up replication in SQL 2005

Im trying to setup replication between two 2005 standard editions. They are on the same LAN and installed equally + SP1. They are installed as named instances (with the same instance name MSSQL01). Remote access is allowed and TCP/IP and shared memory and I can connect between them.

SQL1\MSSQL01 is configured as the publisher & distributor and the snapshot is done. When Im using the subscriber wizard from SQL1 to create the subscriptions and add SQL2\MSSQL01 as a subscriber the connection fails to the remote server.

The error message is:

TITLE: Connect to Server

Cannot connect to SQL2\MSSQL01.


ADDITIONAL INFORMATION:

Failed to connect to server SQL2\MSSQL01. (Microsoft.SqlServer.ConnectionInfo)

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

When I do a manual connection to SQL2 using the server managemnet studio on SQL1 I cannot connect using the instance name e.g. SQL2\MSSQL01, it works only when only using the server name e.g. SQL2. Then I have full access both when using a windows or SQL user.

When trying with using the server name only when setting up the subscription, I got a error saying that I need to use the full name.

TITLE: Connect to Server

Cannot connect to TFMHQARNDE07.


ADDITIONAL INFORMATION:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ' SQL2\MSSQL01'. (Replication.Utilities)

I can also mention that theese servers are cloned from another server and I needed to drop the old servername and add the new servername (sp_addserver) on both servers to get the publisher to work.

When I try to setup the subscription from SQL2 and add SQL1 as a publisher I get the same error.

Any one have any idea what it can be?

I found the problem. I didnt have the SQL Browser service runnning which looks to be needed when using instances. After starting it, it works to connect.

Cannot connect to subscriber

Setup:
- SQL2000 SP3 running on Windows2000 servers.
I have a merge replication running between three identical servers. Two of
the servers, the publisher and subscriber1, receive data from different
sources and are synced every day at 4am. Subscriber2 is a query server only
and is updated by the publisher at 5am.
Replication has been running flawlessly since we upgraded everything nearly
a year ago. This morning the merge agent for Subscriber1 failed with the
following error (XXXX is the server name):
"The process could not connect to Subscriber 'XXXX'.
(Source: XXXX (Agent); Error number: 20084)"
"Cannot generate SSPI context
(Source: XXXX (Data source); Error number: 11004)"
Subscriber2 succeeded. Nothing has changed on the servers, and the logon
credential are the same for each.
I'm at a loss here. Anyone have any idea what might be happening and how to
fix it?
Thanks,
John Steen
There are some patches which seem to be causing this problem. The first
thing I would do is try to connect to the problem machine using SQL
Authentication. This might indicate that someone has changed the server from
mixed authentication to windows. Then try Windows - do you get the SSPI
error there too?
Hilary Cotter
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 Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:ABA30CE3-CA41-481E-9195-35482227D318@.microsoft.com...
> Setup:
> - SQL2000 SP3 running on Windows2000 servers.
> I have a merge replication running between three identical servers. Two
of
> the servers, the publisher and subscriber1, receive data from different
> sources and are synced every day at 4am. Subscriber2 is a query server
only
> and is updated by the publisher at 5am.
> Replication has been running flawlessly since we upgraded everything
nearly
> a year ago. This morning the merge agent for Subscriber1 failed with the
> following error (XXXX is the server name):
> "The process could not connect to Subscriber 'XXXX'.
> (Source: XXXX (Agent); Error number: 20084)"
> "Cannot generate SSPI context
> (Source: XXXX (Data source); Error number: 11004)"
> Subscriber2 succeeded. Nothing has changed on the servers, and the logon
> credential are the same for each.
> I'm at a loss here. Anyone have any idea what might be happening and how
to
> fix it?
> Thanks,
> John Steen
>
|||Thanks for getting back to me so quickly, Hilary.
All three servers are using mixed authentication and identical credentials,
and there have been no patches or updates applied. I ran the merge agent for
Subscriber1 this morning manually and it succeeded. I did the same thing for
Subscriber2 but it failed. A second attempt succeeded. So it doesn't seem
to be an authentication issue.
We have a BU Exec SQL agent that does a differential backup at the same time
the merge agents run, so I'm going to to change the time that the agents run
to see if there's a conflict of some sort. If not, then I'll push it off to
our telecom people.
Thanks,
John
John Steen
remove (nospam) from e-mail address to send me a message
"Hilary Cotter" wrote:

> There are some patches which seem to be causing this problem. The first
> thing I would do is try to connect to the problem machine using SQL
> Authentication. This might indicate that someone has changed the server from
> mixed authentication to windows. Then try Windows - do you get the SSPI
> error there too?
> --
> Hilary Cotter
> 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 Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:ABA30CE3-CA41-481E-9195-35482227D318@.microsoft.com...
> of
> only
> nearly
> to
>
>