FIX: The Distribution Agent May Fail After You Add an Article to an Existing Transactional Publication (325902)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q325902
BUG #: 360937 (SHILOH_BUGS)

SYMPTOMS

After you add an article to an existing transactional publication by using the sp_addarticle replication system stored procedure, the Distribution Agent may fail when it tries to download the snapshot commands for the new article.

NOTE: You may also experience this problem when you add a new article to an existing publication by editing the publication properties in SQL Server Enterprise Manager.

After the failure, you receive the following error message
The process could not read file 'c:\temp\ftp\UTTAMKP01$INST1_pubs_pubs\20020702151436\jobs_1.sch' due to OS error 3.
and in the Distribution Agent Error Details dialog box, you see the following error details:
The system cannot find the path specified.
(Source: (OS); Error number: 3)
This problem occurs only if the following conditions are true:
  • The subscriber must be a pull subscriber to a transactional publication.
  • You must have configured the publication to support FTP snapshot delivery.
  • You must have configured the publication to allow immediate synchronization. To configure this setting, you set the @immediate_sync parameter of the sp_addpublication replication system stored procedure to true.
  • You must have added an article to an existing publication.

CAUSE

This problem occurs because the Distribution Agent was trying to download the snapshot commands for the newly-added article from the default snapshot location as opposed to the other snapshot location.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How To Obtain the Latest SQL Server 2000 Service Pack

NOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.

The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel. 
  Date        Time         Version    Size         File name
  --------------------------------------------------------------------

  19-JUL-2002 21:51:08 PM  8.00.0662  97,276 bytes Qfe360937.sql
  19-JUL-2002 21:51:35 PM  8.00.0662  21,326 bytes Qfe360937_dist.sql
NOTE: Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.


WORKAROUND

To work around this problem, use one of the following methods:
  • Reinitialize the subscription.

    -or-
  • Use PUSH instead of PULL for the subscription.

    -or-
  • Use UNC snapshot capability instead of FTP for snapshot delivery.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

MORE INFORMATION

Support for FTP delivery of snapshot commands for articles that a user adds to an existing publication that the user configured for concurrent snapshot processing is not included in this fix. The support for concurrent snapshot processing will be added to SQL Server 2000 in Service Pack 3 (SP3).

NOTE: You must run the Qfe360937_dist.sql script in the context of your distribution database. If you have more than one distribution database installed on a particular distribution server, you must run the Qfe360937_dist.sql script in the context of each of your distribution databases. If you do not perform this step correctly, the fix will not work.
Modification Type:MinorLast Reviewed:9/27/2005
Keywords:kbHotfixServer kbQFE kbSQLServ2000sp3fix kbbug kbfix kbSQLServ2000preSP3fix KB325902
©2004 Microsoft Corporation. All rights reserved.