BUG: The Distribution Agent or the Merge Agent does not succeed when you replicate stored procedures or views in SQL Server 2000 (832902)

SYMPTOMS

If you configure a Snapshot replication, a Transactional replication, or a Merge replication to replicate stored procedures or views that were created when the QUOTED_IDENTIFIER option was set to OFF, the Distribution Agent or the Merge Agent may not succeed and you may receive one of the following error messages when you apply the snapshot to the subscriber:

Message 1

Invalid column name 'Column Name'.

Message 2

Cannot use empty object or column names. Use a single space if necessary.

Message 3

The name 'Object Name' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

CAUSE

This problem occurs because the Snapshot Agent always sets the QUOTED_IDENTIFIER option to ON, regardless of the actual setting. Therefore, if the stored procedures or views use double quotation marks, the Distribution Agent or the Merge Agent assumes the default behavior of using double quotation marks for identifiers only. Therefore, you receive one or more of the error messages that appear in the "Symptoms" section of this article.

WORKAROUND

To work around this problem, do any one of the following:

Method 1: Remove the double quotation marks
Edit the stored procedures or views that are participating in the replication so that double quotation marks do not appear in the corresponding stored procedures or views.
Method 2: Use Backup and Restore
Synchronize the replication subscriptions by applying the snapshots to the subscriber manually.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
320499 HOW TO: Manually Synchronize Replication Subscriptions by Using Backup or Restore
Method 3: Use Data Transformation Services (DTS)
Synchronize the replication subscriptions by using the DTS packages to move the stored procedures or views that contain double quotation marks to the subscribers. To synchronize the subscriptions regularly, schedule the DTS packages accordingly.

For additional information about DTS, click the following article number to view the article in the Microsoft Knowledge Base:
242377 INF: How to Use Data Transformation Services (DTS)

MORE INFORMATION

In Microsoft SQL Server 2000, although the stored procedures or views that are participating in the replication are created with the QUOTED_IDENTIFIER option set to OFF, the Snapshot Agent creates the snapshot files and sets the QUOTED_IDENTIFIER option to ON for the stored procedures or views.

Steps to reproduce the behavior

Start SQL Query Analyzer and connect to your instance of SQL Server 2000.
In the Pubs database, and with the QUOTED_IDENTIFIER option set to OFF, create a new table that is named table1, create a new stored procedure that is named proc1, and then create a new view that is named view1. To do this, run the following script by using SQL Query Analyzer:
```
USE Pubs
GO

CREATE TABLE table1 
(
	id int PRIMARY KEY
)
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE VIEW view1 
AS 
	SELECT id, 'col2'= "a" FROM table1
GO

CREATE PROCEDURE proc1
AS
	INSERT INTO table1 WITH (ROWLOCK) (id) VALUES ("1")
GO
```
Use Merge Replication to create a new publication that publishes the articles table1, proc1, and view1.
In the Northwind database, create a new push subscription for the publication that you created in step 3.
Run the corresponding Snapshot Agent.
Run the Merge Agent.

The Merge Agent does not succeed when it tries to apply the initial snapshot to the Subscriber. You may receive the following error message in the Merge Agent:
Error Message:

The schema script 'Path of the .sch file for the stored procedure proc1' could not be propagated to the subscriber.

Error Details:

The schema script '<Path to .sch file for the stored procedure proc1>' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
---------------------------------------------------------------------------------------------------------------
The name '1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
(Source: <Publisher> (Data source); Error number: 128)
---------------------------------------------------------------------------------------------------------------