Microsoft KB Archive/278324

= BUG: Distribution Agent Fails During Initial Synchronization When You Use Stored Procedure Replication =

Article ID: 278324

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



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.</ul>

<div class="status_section">

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

<div class="moreinformation_section">

MORE INFORMATION
This behavior does not occur when the Distributor server is running Microsoft SQL Server 2000.

Keywords: kbbug kbpending KB278324

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.