Article ID: 278324 - Last Review: October 16, 2002 - Revision: 1.1

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

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

This article was previously published under Q278324

BUG #: 54317 (SQLBUG_70)

Expand all | Collapse all

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 ([]).

Back to the top

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.

Back to the top

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.

Back to the top