updating subscribers error for transactional replication

Last Post 03 Aug 2006 07:46 PM by bchu1981. 12 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
bchu1981
New Member
New Member

--
03 Aug 2006 01:41 AM
Dear all,

I have successfully configured transaction replication without updating subscribers. But if use updating subscribers, it was not successful, either queued update or immediate update.

For the queued updating, error message is: ODBC Error:Could not change the Publisher because the subscription has been dropped. Use sp_subscription_cleanup to clean up the triggers.

For the immediate updating, error message is: New transaction cannot enlist in the specified transaction coordinator.

Any response is highly appreciated! Thank you.

bchu
bchu1981
New Member
New Member

--
03 Aug 2006 07:40 PM
rm, Thanks!

Have some progress, I succeed in two way transactional replication between two servers within the same domain. But I can't do it for two servers residing in two seperate domains, although trust relationship is established between the two.

Both use sql 2000, sp4, win 2003, sp1. MSDTC is on and set to allow network transactions. Both can ping each other. The two servers resides in two domains and two networks connecting by VPN. In VPN the rule is set to allow any traffic between these two private networks.

Thanks again! Very nice to see friendly help!

bchu1981
New Member
New Member

--
03 Aug 2006 07:46 PM
If I update from subscriber, the error is "New transaction cannot enlist int the specified transaction coordinator [0x8004d00a]".
bchu1981
New Member
New Member

--
06 Aug 2006 10:06 PM
rm, Thanks for your reply.
In the event viewer, there are some errors about "DCOM activation" for the network service account. After I modified the setting, the error disappear. However, still I can't use the MSDTC.

I have set "no authentication required", but it does not work. I tried to change the registry for msdtc, neither does it work . Do you mean the "no authentication required" under the "security configuration" under the "MSDTC" tab of my "computer configuration" in the "component services"?

I have a machine that it does work for the MSDTC, but I noticed it is win 2003 without sp1. Other machines have sp1 so they can't work.

By the way, I also the following code to validate, is it ok?
set xact_abort ON
go
use pubs
go
begin distributed transaction
select * from [remote_machine_name].pubs.dbo.authors
commit tran
go

Thanks!
bchu1981
New Member
New Member

--
06 Aug 2006 11:16 PM
HI, have some progress.

Somehow the "New transaction cannot enlist int the specified transaction coordinator [0x8004d00a]" disappear. However, the update is still not successful. The new error is "Could not change the publisher because the subscription has been dropped. Use sp_subscription_cleanup to clean up the triggers".

What is it? Thanks!
bchu1981
New Member
New Member

--
07 Aug 2006 05:04 PM
rm, Thanks again!

I have re-created the publication and subscription, but it doesn't help much.

I have make sure the two tables are identical even with the same collations. Two machines are within almost identical system clocks.

I don't know why still it is not successful.

Thanks,
bchu
bchu1981
New Member
New Member

--
07 Aug 2006 05:05 PM
rm, Thanks again!

I have re-created the publication and subscription, but it doesn't help much.

I have make sure the two tables are identical even with the same collations. Two machines are within almost identical system clocks.

I don't know why still it is not successful.

Thanks,
bchu
bchu1981
New Member
New Member

--
08 Aug 2006 10:23 PM
rm, thanks again!

The scripts generated is as follows:


-- Enabling the replication database
use master
GO

exec sp_replicationdboption @dbname = N'Northwind', @optname = N'publish', @value = N'true'
GO

use [Northwind]
GO

-- Adding the transactional publication
exec sp_addpublication @publication = N'Northwind', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication of Northwind database from Publisher DB01.', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'true', @autogen_sync_procs = N'true', @retention = 336, @allow_queued_tran = N'true', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false', @conflict_policy = N'pub wins', @centralized_conflicts = N'true', @conflict_retention = 14, @queue_type = N'sql', @add_to_active_directory = N'false', @logreader_job_name = N'DB01-Northwind-19', @qreader_job_name = N'[DB01].14'
exec sp_addpublication_snapshot @publication = N'Northwind',@frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 223600, @active_end_time_of_day = 0, @snapshot_job_name = N'DB01-Northwind-Northwind-31'
GO

exec sp_grant_publication_access @publication = N'Northwind', @login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'Northwind', @login = N'distributor_admin'
GO
exec sp_grant_publication_access @publication = N'Northwind', @login = N'NX\Administrator'
GO
exec sp_grant_publication_access @publication = N'Northwind', @login = N'sa'
GO

-- Adding the transactional articles
exec sp_addarticle @publication = N'Northwind', @article = N'Supervisory', @source_owner = N'dbo', @source_object = N'Supervisory', @destination_table = N'Supervisory', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000000CEF7, @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_Supervisory', @del_cmd = N'XCALL sp_MSdel_Supervisory', @upd_cmd = N'XCALL sp_MSupd_Supervisory', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO

-- Adding the transactional subscription
exec sp_addsubscription @publication = N'Northwind', @article = N'all', @subscriber = N'192.168.2.3', @destination_db = N'QS.NX', @sync_type = N'automatic', @update_mode = N'failover', @offloadagent = 0, @dts_package_location = N'distributor'
GO

Looking forward to your insightful suggestions.

bchu
bchu1981
New Member
New Member

--
09 Aug 2006 06:28 PM
rm, thanks again!

The two domains are trusted by each other.

Currently I am setting the appropriate permission for the sql folders and registries. It is still not successful. I suspend some of my setting is not correct, and currently I don't want to change it because it is a production server. Now I am install testing machine for this purpose.

But I have some questions. Do both the MSSQLSERVER and SQLAGENTSERVER need the the same domain account? Since the starting account of MSSQLSERVER of the publisher is set to local system account, I suspect this is the reason for failure. But I don't want to change it for now.

By the way, could you explain what is "replicate via dts packages"?

Thanks!
bchu
bchu1981
New Member
New Member

--
10 Aug 2006 09:19 PM
HI, RM, thanks very much for your warm-hearted replies!

I have figured out this problem. The major issue is that I need to register server using their NETBIOS names not IPs. I am inspired by the setting in sql 2005 where no IP is allowed for replication process.

Thanks very much.

By the way, do you use msn? If so, please add me to your list. bingchenghu@yahoo.com.

Nice to talk to you.

bchu
bchu1981
New Member
New Member

--
11 Aug 2006 04:35 PM
ok, what do you use then?
bchu1981
New Member
New Member

--
13 Aug 2006 07:33 PM
rm, thanks anyway!
bchu1981
New Member
New Member

--
13 Aug 2006 07:33 PM
rm, thanks anyway!


Acceptable Use Policy
---