BUG: Distribution Agent Fails During Initial Synchronization When You Use Stored Procedure Replication (278324)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q278324
BUG #: 54317 (SQLBUG_70)

SYMPTOMS

When you have stored procedure articles as part of a Snapshot or Transactional publication, the Distribution agent may fail with the following error message when applying the initial snapshot to the Subscriber:
Incorrect syntax near 'Stored_Procedure_Name'
This behavior occurs if the stored procedures that are being replicated are created with the SET QUOTED_IDENTIFIER option set to OFF and the identifiers for the stored procedure are enclosed in square brackets ([]).

CAUSE

The Distribution agent replaces square brackets ([]) in an identifier with double quotes. The double quotes cause the stored procedure schema script to fail on the Subscriber.

WORKAROUND

To work around this problem, you can either:
  • Alter the stored procedure schema (.sch) file in the snapshot folder by:

    • Replacing all occurrences of SET QUOTED_IDENTIFIER OFF with SET QUOTED_IDENTIFIER ON.

      -or-

    • Removing the square brackets ([]) that are around the stored procedure identifiers that are not delimited.
    -or-
  • Remove the stored procedure article from the publication, and then re-create the stored procedure with the SET QUOTED_IDENTIFIER option set to ON.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.

MORE INFORMATION

This behavior does not occur when the Distributor server is running Microsoft SQL Server 2000.
Modification Type:MajorLast Reviewed:10/16/2002
Keywords:kbBug kbDSupport KB278324
©2002 Microsoft Corporation. All rights reserved.