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
SET QUOTED_IDENTIFIER ON
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?