0
Mrdenny 
46795 pts. | Aug 7 2009 4:26PM GMT
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.
DNG 55 pts. | Aug 8 2009 5:51AM GMT
Hi Mrdenny,
I’m a novice of SQL Server. Could you show me the steps or command of your suggestion. Thanks.
mrdenny 46795 pts. | Aug 10 2009 8:06PM GMT
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.
DNG 55 pts. | Aug 11 2009 3:40AM GMT
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’)) & 0×1 = 0×1 then 0 else 1 end as bit) as DB_CaseSensitive,cast(case when convert (int,serverproperty (’comparisonstyle’)) & 0×1 = 0×1 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 <a href="http://SC.name" title="http://SC. " target="_blank">SC.name</a> = ‘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: 0×0x0000000000000000000000000000
2009-08-11 03:32:36.281 Last transaction timestamp: 0×0000000000000000000000000000
Transaction seqno: 0×0001189800000066000300000008
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: 0×0001189800000066000300000008
Transaction seqno: 0×0001189800000066000300000008
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: 0×0001189800000066000300000008
Transaction seqno: 0×0001189800000066000300000008
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: 0×0001189800000066000300000008
Transaction seqno: 0×0001189800000066000300000008
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: 0×0001189800000066000300000008
Transaction seqno: 0×0001189800000066000300000008
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: 0×0001189800000066000300000008
Transaction seqno: 0×0001189800000066000300000008
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′
mrdenny 46795 pts. | Aug 11 2009 8:43PM GMT
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.
DNG 55 pts. | Aug 12 2009 4:03AM GMT
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.
Mrdenny 46795 pts. | Aug 12 2009 8:38PM GMT
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.
DNG 55 pts. | Aug 17 2009 7:30AM GMT
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.
