Quoted identifier on

Last Post 15 Jun 2005 07:56 AM by ROBCOL1305. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
New Member
New Member

15 Jun 2005 07:56 AM
I am having problems with Replication in that I have a stored proceedure in the published DB that declares some SET @strSQL items and uses quotation marks to build a select statement.

When a snapshot is taken it creates the SCH files with a


at the top of the script, drops the SP and then recreates it. This 'SET QUOTED_' bit is NOT in the original SP.

When the snapshot is being applied to the subscriber it fails because during the creation of the SP, it produces an error 207 and claims that the columns are invalid.

I have discovered that the SET QUOTED_IDENTIFIER ON statement is in effect, preventing the creatation of the SP because of the quotation marks.

How can exclude the QUOTED_IDENTIFIER ON command when the snapshot is created?

Update: I found this
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. To get round this, you can change the object script to refer to literals using single quotes, or use DTS to transfer the objects.

What do you think the 'Object Script' refere to? The SP or the Replication generated sch file?
You are not authorized to post a reply.

Acceptable Use Policy