SQL Server 2005 – Unable to Eun a Pull Subscription due to “unable to connect to distributor” error

55 pts.
Tags:
SQL Server 2005
SQL Server replication
I have 2 Windows server (Windows Server 2003) running SQL Server 2005. Server 1 is the distirbutor and publisher. For a snap-shot replication, I have no problems running a push subscription from server 1 to server 2. However when I try to set up and run a pull subscription on server 2. It fails due to not being able to connect to the distributor on server 1. However, it works while I setup another push subscription on another instance on server 1.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Can you use SSMS on Server2 to connect to the SQL Instance on Server1?

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DNG
    Yes, I can connect to server 1 thru SSMS.
    55 pointsBadges:
    report
  • Denny Cherry
    Try running the distribution agent from the command line on the subscriber and turn up the logging level and see if it can give you some more information about why it can't connect.
    66,130 pointsBadges:
    report
  • DNG
    Hi Mrdenny, I'm a novice of SQL Server. Could you show me the steps or command of your suggestion. Thanks.
    55 pointsBadges:
    report
  • Denny Cherry
    Edit the replication job in SSMS and copy all the parameters show. On the Subscriber navigate to C:Program FilesMicrosoft SQL Server90Com (the path may be different depending on how you set everything up) in a command prompt window. Type in "distrib.exe" then a space then paste in the parameters you copied from the SQL Agent job. One of those parameters should be a logging level. Increase the level to 3 or 4 and see what information it spits out. You will need to stop the Replication Agent on the distributor before running the command in the command window.
    66,130 pointsBadges:
    report
  • DNG
    Hi Mrdenny, Below is the output of running "distrib.exe" in a command prompt widnow. Could you find any clue after analysis. Thanks. 2009-08-11 03:32:35.500 Microsoft SQL Server Distribution Agent 9.00.4035.00 2009-08-11 03:32:35.515 Copyright (c) 2005 Microsoft Corporation 2009-08-11 03:32:35.515 2009-08-11 03:32:35.515 The timestamps prepended to the output lines are expressed in terms of UTC time. 2009-08-11 03:32:35.515 User-specified agent parameter values: -Publisher DCLSVR1 -PublisherDB POSTHDB -Publication REPLICATE TO SECOND INSTANCE -Distributor DCLSVR1 -SubscriptionType 1 -Subscriber DCLSVR3 -SubscriberSecurityMode 1 -SubscriberDB HKPOSTHDB -HistoryVerboseLevel 3 -OutputVerboseLevel 2 -Output c:replication.log 2009-08-11 03:32:35.531 Connecting to Subscriber 'DCLSVR3' 2009-08-11 03:32:35.531 Connecting to OLE DB Subscriber at datasource: 'DCLSVR3', location: '', catalog: 'HKPOSTHDB', providerstring: '' using provider 'SQLNCLI' 2009-08-11 03:32:35.718 OLE DB Subscriber: DCLSVR3 DBMS: Microsoft SQL Server Version: 09.00.4035 catalog name: HKPOSTHDB user name: dbo API conformance: 0 SQL conformance: 0 transaction capable: 1 read only: F identifier quote char: " non_nullable_columns: 0 owner usage: 15 max table name len: 128 max column name len: 128 need long data len: max columns in table: 1000 max columns in index: 16 max char literal len: 131072 max statement len: 131072 max row size: 131072 2009-08-11 03:32:35.718 OLE DB Subscriber: DCLSVR3 DBMS: Microsoft SQL Server Version: 09.00.4035 catalog name: HKPOSTHDB user name: dbo API conformance: 0 SQL conformance: 0 transaction capable: 1 read only: F identifier quote char: " non_nullable_columns: 0 owner usage: 15 max table name len: 128 max column name len: 128 need long data len: max columns in table: 1000 max columns in index: 16 max char literal len: 131072 max statement len: 131072 max row size: 131072 2009-08-11 03:32:35.718 OLE DB Subscriber 'DCLSVR3': {call sp_helpsubscription_properties (N'DCLSVR1', N'POSTHDB', N'REPLICATE TO SECOND INSTANCE')} 2009-08-11 03:32:35.718 Connecting to Distributor 'DCLSVR1' 2009-08-11 03:32:35.734 Connecting to OLE DB Distributor at datasource: 'DCLSVR1', location: '', catalog: '', providerstring: '' using provider 'SQLNCLI' 2009-08-11 03:32:35.765 OLE DB Distributor: DCLSVR1 DBMS: Microsoft SQL Server Version: 09.00.3042 catalog name: user name: dbo API conformance: 0 SQL conformance: 0 transaction capable: 1 read only: F identifier quote char: " non_nullable_columns: 0 owner usage: 15 max table name len: 128 max column name len: 128 need long data len: max columns in table: 1000 max columns in index: 16 max char literal len: 131072 max statement len: 131072 max row size: 131072 2009-08-11 03:32:35.781 OLE DB Distributor 'DCLSVR1': exec sp_helpdistpublisher N'DCLSVR1' 2009-08-11 03:32:35.781 OLE DB Distributor 'DCLSVR1': select @@SERVERNAME 2009-08-11 03:32:35.781 OLE DB Distributor: DCLSVR1 DBMS: Microsoft SQL Server Version: 09.00.3042 catalog name: distribution user name: dbo API conformance: 0 SQL conformance: 0 transaction capable: 1 read only: F identifier quote char: " non_nullable_columns: 0 owner usage: 15 max table name len: 128 max column name len: 128 need long data len: max columns in table: 1000 max columns in index: 16 max char literal len: 131072 max statement len: 131072 max row size: 131072 2009-08-11 03:32:35.781 OLE DB Distributor 'DCLSVR1': execute sp_server_info 18 2009-08-11 03:32:35.781 ANSI codepage: 1 OLE DB Distributor 'DCLSVR1': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'DCLSVR1') 2009-08-11 03:32:35.796 OLE DB Distributor 'DCLSVR1': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'DCLSVR3') 2009-08-11 03:32:35.796 OLE DB Distributor 'DCLSVR1': execute sp_MShelp_profile 15, 3, N'' 2009-08-11 03:32:35.796 Parameter values obtained from agent profile: -bcpbatchsize 2147473647 -commitbatchsize 100 -commitbatchthreshold 1000 -keepalivemessageinterval 300 -logintimeout 15 -maxbcpthreads 1 -maxdeliveredtransactions 0 -pollinginterval 5000 -querytimeout 1800 -skiperrors -transactionsperhistory 100 2009-08-11 03:32:35.812 OLE DB Subscriber 'DCLSVR3': execute sp_server_info 18 2009-08-11 03:32:35.812 ANSI codepage: 1 OLE DB Subscriber 'DCLSVR3': 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',cast(case when convert (int,databasepropertyex (@dbname,'comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as DB_CaseSensitive,cast(case when convert (int,serverproperty ('comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as Server_CaseSensitive set nocount off 2009-08-11 03:32:35.843 OLE DB Distributor 'DCLSVR1': {call sys.sp_MSispeertopeeragent (?,?)} 2009-08-11 03:32:35.859 OLE DB Subscriber 'DCLSVR3': exec sp_MSreplcheck_subscribe 2009-08-11 03:32:35.874 OLE DB Subscriber 'DCLSVR3': exec sp_MScreate_sub_tables @tran_sub_table = 1, @property_table = 0, @p2p_table = 0 2009-08-11 03:32:36.062 OLE DB Subscriber 'DCLSVR3': if exists (select * from sysindexes where id = object_id('MSreplication_subscriptions') and name = 'uc1MSReplication_subscriptions') begin if not exists (select * from sysindexes SI join sysindexkeys SIC on SI.id = SIC.id and SI.indid = SIC.indid join syscolumns SC on SI.id = SC.id and SC.colid = SIC.colid where SI.id = object_id('MSreplication_subscriptions') and SC.name = 'transaction_timestamp') begin drop index MSreplication_subscriptions.uc1MSReplication_subscriptions CREATE UNIQUE CLUSTERED INDEX uc1MSReplication_subscriptions ON MSreplication_subscriptions(publication, publisher_db, publisher, subscription_type, transaction_timestamp) end end 2009-08-11 03:32:36.078 OLE DB Subscriber 'DCLSVR3': if COLUMNPROPERTY( OBJECT_ID('MSreplication_subscriptions'),'distribution_agent','AllowsNull') <> 1 BEGIN alter table MSreplication_subscriptions alter column distribution_agent sysname null end 2009-08-11 03:32:36.093 OLE DB Subscriber 'DCLSVR3': exec sp_MSinit_subscription_agent @publisher = N'DCLSVR1',@publisher_db = N'POSTHDB', @publication = N'REPLICATE TO SECOND INSTANCE', @subscription_type = 1 2009-08-11 03:32:36.109 Connecting to OLE DB Distributor at datasource: 'DCLSVR1', location: '', catalog: 'distribution', providerstring: '' using provider 'SQLNCLI' 2009-08-11 03:32:36.218 OLE DB Distributor: DCLSVR1 DBMS: Microsoft SQL Server Version: 09.00.3042 catalog name: distribution user name: dbo API conformance: 0 SQL conformance: 0 transaction capable: 1 read only: F identifier quote char: " non_nullable_columns: 0 owner usage: 15 max table name len: 128 max column name len: 128 need long data len: max columns in table: 1000 max columns in index: 16 max char literal len: 131072 max statement len: 131072 max row size: 131072 2009-08-11 03:32:36.234 OLE DB Subscriber 'DCLSVR3': {call sp_MSensure_single_instance(N'DCLSVR1-POSTHDB-REPLICATE TO SECOND I-DCLSVR3-15', 10)} 2009-08-11 03:32:36.249 Initializing 2009-08-11 03:32:36.265 OLE DB Subscriber 'DCLSVR3': exec sp_MSset_subscription_properties @publisher = N'DCLSVR1',@publisher_db = N'POSTHDB', @publication = N'REPLICATE TO SECOND INSTANCE', @subscription_type = 1, @allow_subscription_copy = 0, @queue_id = N'', @update_mode = 0, @attach_version = 0xe1583077db56f344899db80d7299f504, @queue_server = N'' 2009-08-11 03:32:36.265 OLE DB Subscriber 'DCLSVR3': exec dbo.sp_MSupdatelastsyncinfo N'DCLSVR1',N'POSTHDB', N'REPLICATE TO SECOND INSTANCE', 1, 1, N'Synchronization in progress' 2009-08-11 03:32:36.265 OLE DB Distributor 'DCLSVR1': {call sp_MSget_subscription_guid(15)} 2009-08-11 03:32:36.281 sp_MSget_repl_commands timestamp value is: 0x0x0000000000000000000000000000 2009-08-11 03:32:36.281 Last transaction timestamp: 0x0000000000000000000000000000 Transaction seqno: 0x0001189800000066000300000008 Command Id: 1 Partial: 0 Type: 50 Command: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncDCLSVR1_POSTHDB_REPLICATE TO SECOND INSTANCE20090811112285 2009-08-11 03:32:36.281 Last transaction timestamp: 0x0001189800000066000300000008 Transaction seqno: 0x0001189800000066000300000008 Command Id: 2 Partial: 0 Type: 51 Command: 2009-08-11 03:32:36.281 OLE DB Subscriber 'DCLSVR3': sp_MSacquiresnapshotdeliverysessionlock 2009-08-11 03:32:36.281 Last transaction timestamp: 0x0001189800000066000300000008 Transaction seqno: 0x0001189800000066000300000008 Command Id: 4 Partial: 0 Type: 60 Command: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncDCLSVR1_POSTHDB_REPLICATE TO SECOND INSTANCE20090811112285POSPLU_2.pre 2009-08-11 03:32:36.281 Last transaction timestamp: 0x0001189800000066000300000008 Transaction seqno: 0x0001189800000066000300000008 Command Id: 5 Partial: 0 Type: 2 Command: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncDCLSVR1_POSTHDB_REPLICATE TO SECOND INSTANCE20090811112285POSPLU_2.sch 2009-08-11 03:32:36.281 OLE DB Subscriber 'DCLSVR3': sp_MStrypurgingoldsnapshotdeliveryprogress 2009-08-11 03:32:36.296 OLE DB Subscriber 'DCLSVR3': sp_MSissnapshotitemapplied @snapshot_session_token = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncDCLSVR1_POSTHDB_REPLICATE TO SECOND INSTANCE20090811112285', @snapshot_progress_token = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncDCLSVR1_POSTHDB_REPLICATE TO SECOND INSTANCE20090811112285POSPLU_2.pre' 2009-08-11 03:32:36.296 Last transaction timestamp: 0x0001189800000066000300000008 Transaction seqno: 0x0001189800000066000300000008 Command Id: 6 Partial: 0 Type: 102 Command: [ImmediateExecution]ALTER TABLE [dbo].[POSPLU] ADD CONSTRAINT [PK_POSPLU] PRIMARY KEY CLUSTERED ([BrandGroupID],[BarCode]) 2009-08-11 03:32:36.296 Last transaction timestamp: 0x0001189800000066000300000008 Transaction seqno: 0x0001189800000066000300000008 Command Id: 7 Partial: 0 Type: 2 Command: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncDCLSVR1_POSTHDB_REPLICATE TO SECOND INSTANCE20090811112285POSPLU_2.idx 2009-08-11 03:32:36.296 OLE DB Subscriber 'DCLSVR3': sp_MSreleasesnapshotdeliverysessionlock 2009-08-11 03:32:36.312 The process could not read file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncDCLSVR1_POSTHDB_REPLICATE TO SECOND INSTANCE20090811112285POSPLU_2.pre' due to OS error 3. 2009-08-11 03:32:36.312 ErrorId = 23, SourceTypeId = 2 ErrorCode = '20203' ErrorText = 'The process could not read file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncDCLSVR1_POSTHDB_REPLICATE TO SECOND INSTANCE20090811112285POSPLU_2.pre' due to OS error 3.' 2009-08-11 03:32:36.312 Adding alert to msdb..sysreplicationalerts: ErrorId = 23, Transaction Seqno = 0001189800000066000300000008, Command ID = 4 Message: Replication-Replication Distribution Subsystem: agent DCLSVR1-POSTHDB-REPLICATE TO SECOND I-DCLSVR3-15 failed. The process could not read file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncDCLSVR1_POSTHDB_REPLICATE TO SECOND INSTANCE20090811112285POSPLU_2.pre' due to OS error 3.ErrorId = 23, SourceTypeId = 3 ErrorCode = '3' ErrorText = 'The system cannot find the path specified. ' 2009-08-11 03:32:36.327 Category:OS Source: Number: 3 Message: The system cannot find the path specified. 2009-08-11 03:32:36.327 ErrorId = 23, SourceTypeId = 1 ErrorCode = '' ErrorText = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncDCLSVR1_POSTHDB_REPLICATE TO SECOND INSTANCE20090811112285POSPLU_2.pre' 2009-08-11 03:32:36.327 OLE DB Subscriber 'DCLSVR3': exec dbo.sp_MSupdatelastsyncinfo N'DCLSVR1',N'POSTHDB', N'REPLICATE TO SECOND INSTANCE', 1, 6, N'The process could not read file ''C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncDCLSVR1_POSTHDB_REPLICATE TO ' 2009-08-11 03:32:36.327 Disconnecting from OLE DB Subscriber 'DCLSVR3' 2009-08-11 03:32:36.343 Disconnecting from OLE DB Subscriber 'DCLSVR3' 2009-08-11 03:32:36.343 Disconnecting from OLE DB Distributor 'DCLSVR1' 2009-08-11 03:32:36.343 Disconnecting from OLE DB Distributor 'DCLSVR1'
    55 pointsBadges:
    report
  • Denny Cherry
    It appears that you are trying to apply the snapshot to the subscriber. Because the agent was configured with a local path you need to run it on the subscriber. You are getting "The system cannot find the path specified." errors when it's searching the "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncDCLSVR1_POSTHDB_REPLICATE TO SECOND INSTANCE" (that path might not be totally accurate, I had to guess on where the s went). Run the distribution agent on the distributor until the snapshot has been completely loaded. That or copy the snapshot files into a folder on the subscriber in the EXACT same path that it is in on the distributor then run the agent again.
    66,130 pointsBadges:
    report
  • DNG
    Hi Mrdenny, The replication completed successfully after copied the snapshot files onto the subscriber server. Is it possible to configure the push replication process to make it automatically get the snapshot files from distribution server instead of copying it manually because I think it’s unreasonable. Thank you for your kind assistance.
    55 pointsBadges:
    report
  • Denny Cherry
    That's the catch here. When you moved the Agent from the distributor you changed it from a Push subscription to a pull subscription. When configuring a push subscription you can enter a local path for the snapshot storage. When configuration a pull subscription you have to configure a network share, and use the UNC path for the path for the snapshot storage. This way all the servers have access to the files. Otherwise you get into the situation that you where in. You should be able to change this setting after the fact in the publication editor so that the next snapshot that is takes is available from the network share.
    66,130 pointsBadges:
    report
  • DNG
    Hi Mrdenny, The pull replication completed successfully after created a network share and using the UNC path for the snapshot folder on the distribution server. Thank you very much for your kind assistance.
    55 pointsBadges:
    report
  • Denny Cherry
    No problem, happy to help.
    66,130 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following