Recently I was contacted by a client who migrated a new Stored Procedure to production and wondered why replication all of a sudden ‘broke’ when the new Stored Procedure was added to the publication. The reason is that the Stored Procedure was using Quoted Identifiers ie. double quotation marks (“a”).
This problem occurred because the Replication Snapshot Agent sets the QUOTED_IDENTIFIER option to ON, regardless of the actual setting. Hence as the Stored Procedures was using double quotation marks, the default behaviour of the Distribution Agent was to use double quotation marks for identifiers only.
The following TSQL script illustrates this issue:
— Will Fail
SET QUOTED_IDENTIFIER ON
GO
IF “a” = “a” PRINT ‘does not work’
GO
— Will Succeed
SET QUOTED_IDENTIFIER OFF
GO
IF “a” = “a” PRINT ‘works’
GO
