Replication error - ' unable to begin a distributed transaction''

Summary :

I am trying to setup replication between 2 servers (both Win2008 Server OS and SQL v2008) as 'transactional with subscriber updates'.
When I setup the subscriper server I get the error:
      'SQLNCLI for linked server "Site_SQL02" was unable to begin a distributed transaction'
      'SQLNCLI for linked server "Site_SQL02" returned message "No transaction is active" error - 7391'
      

But if I setup merge replication it works fine between these two servers.

Or when I setup the same (transactional replication with subscription) between this publisher and a different SQLServer (Win2003 OS Server and Sql v2005) subscriber it works fine.

Detail:


The 2 servers are in the same domain but at different physical offices connected by a 2mbps satellite connection (not that fast, has other apps on that bandwidth).



I setup up the distributor (Win2008 Server, SQL Std v2008 SP1) Site_SQL02:
      - turned off the firewall
      - turned on DTS, enabled remote
      - SQLAgent running
      - using SSMS, setup this SQLServer as a distributor
      (SQL wizard - Configure Distributor : sets up the trans db in another folder and I setup that folder as a shared network folder. The share permissions are Everyone=Full)
      - using SSMS, created new publication 'Test04_trans'. This is a 'transactional with updates subscription, Subscriber pull'. The publication item is a single very simple table. The wizard added a field to this 'msrepl_tran_version'
      
      The script for this is shown in code window below. This publication procedure and script works successfully

I setup up the subscriber (Win2008 Server, SQL Std v2008 SP1) Office_SQL02:
      - turned off the firewall
      - turned on DTS, enabled remote
      - SQLAgent running

      - using SSMS, I am sitting on the Site_SQL02 box and in SSMS I Connect now to the other SQLServer, 'Office_SQL02'
      - whilst connected to this subscriber, Office_SQL02, created new subscription to the above publisher. The script for this is shown in code window below.
      
      This takes quite a while to run, like 4 to 5 mins. When it completes I get the error:
      
      

            TITLE: New Subscription Wizard
            ------------------------------

            SQL Server could not create a subscription for Subscriber 'Office_SQL01'.

            ------------------------------
            ADDITIONAL INFORMATION:

            An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

            ------------------------------

            The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "REPLLINK_Site_SQL02750965912_TESTRPLCN_-1284163390_TEST04_TRA2138751039_TESTRPLCN_312375228" was unable to begin a distributed transaction.
            Changed database context to 'TestRplcn_Subscriber'.
            OLE DB provider "SQLNCLI10" for linked server "REPLLINK_Site_SQL02750965912_TESTRPLCN_-1284163390_TEST04_TRA2138751039_TESTRPLCN_312375228" returned message "No transaction is active.". (Microsoft SQL Server, Error: 7391)

            
What I have troubleshooted:

      - tried setting up as a Push subscription (Site push to Office instead of pull). I get same error as above.
      
      - tried setting up the subscriber same as above, a Pull subscription, but this time while sitting at Office_SQL02 terminal and using SSMS direct on the machine there rather than setting up while sitting at Site_SLQ02, I get this error:
      
            TITLE: New Subscription Wizard
            ------------------------------

            SQL Server could not create a subscription for Subscriber 'Office_SQL02'.

            ------------------------------
            ADDITIONAL INFORMATION:

            An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

            ------------------------------

            Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "REPLLINK_Site_SQL02750965912_TESTRPLCN_-1284163390_TEST04_TRA2138751039_TESTRPLCN_312375228".
            Changed database context to 'TestRplcn_Subscriber'.
            OLE DB provider "SQLNCLI10" for linked server "REPLLINK_Site_SQL02750965912_TESTRPLCN_-1284163390_TEST04_TRA2138751039_TESTRPLCN_312375228" returned message "Unable to complete login process due to delay in opening server connection". (Microsoft SQL Server, Error: 7303)


      
      - I CAN setup merge subscription between Site_SQL02 and Office_SQL02 and it works. But I would like to use 'transactional with updates subscription, Subscriber pull' because our tables are very large and can change a lot and this is likely more appropriate to our setup.
      
      - I CAN setup a subscriber on another machine, a Win2003 Server with SQL Std v2005 (not v2008) and it sits in the same physical office (so not accessed across the satellite link), to the publisher (no change in the publication) and the replication works fine, both ways. I did this the exact same way as for Office_SQL02.
      

Any ideas?

Also, do I need SQL Browser service running?

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
 
/ ** setup the new publcation on Site_SQL02 **/
 
use [TestRplcn_Publisher]
exec sp_replicationdboption @dbname = N'TestRplcn_Publisher', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [TestRplcn_Publisher]
exec sp_addpublication @publication = N'Test04_trans', 
	@description = N'Transactional publication with updatable subscriptions of database ''TestRplcn_Publisher'' from Publisher ''Site_SQL02''.', 
	@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'true', 
	@autogen_sync_procs = N'true', @allow_queued_tran = N'true', @allow_dts = N'false', 
	@conflict_policy = N'pub wins', @centralized_conflicts = N'true', @conflict_retention = 14, @queue_type = N'sql', @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'Test04_trans', @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 = 0, @publisher_login = N'sa_Luke', @publisher_password = N''
 
 
use [TestRplcn_Publisher]
exec sp_addarticle @publication = N'Test04_trans', @article = N'tblTest01_trans', @source_owner = N'dbo', @source_object = N'tblTest01_trans',
	@type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008035CDF, 
	@identityrangemanagementoption = N'manual', @destination_table = N'tblTest01_trans', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false'
GO
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
 
 
-----------------BEGIN: Script to be run at Publisher 'Site_SQL02'-----------------
use [TestRplcn_Publisher]
exec sp_addsubscription @publication = N'Test04_trans', @subscriber = N'Office_SQL02', @destination_db = N'TestRplcn_Subscriber', @sync_type = N'Automatic', 
	@subscription_type = N'pull', @update_mode = N'queued failover'
GO
-----------------END: Script to be run at Publisher 'Site_SQL02'-----------------
 
-----------------BEGIN: Script to be run at Subscriber 'Office_SQL02'-----------------
use [TestRplcn_Subscriber]
exec sp_addpullsubscription @publisher = N'Site_SQL02', @publication = N'Test04_trans', @publisher_db = N'TestRplcn_Publisher', @independent_agent = N'True', 
	@subscription_type = N'pull', @description = N'', @update_mode = N'queued failover', @immediate_sync = 1
exec sp_link_publication @publisher = N'Site_SQL02', @publication = N'Test04_trans', @publisher_db = N'TestRplcn_Publisher', 
	@security_mode = 0, @login = N'sa_Luke', @password = null
exec sp_addpullsubscription_agent @publisher = N'Site_SQL02', @publisher_db = N'TestRplcn_Publisher', @publication = N'Test04_trans',
	 @distributor = N'Site_SQL02', @distributor_security_mode = 0, 
	 @distributor_login = N'sa_Luke', @distributor_password = null, 
	 @enabled_for_syncmgr = N'False', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 1, @frequency_subday = 2, 
	 @frequency_subday_interval = 30, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090630, @active_end_date = 99991231, 
	 @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
-----------------END: Script to be run at Subscriber 'Office_SQL02'-----------------

Select allOpen in new window