In yesterdays post I mused what surprises replication would have for me after resolving an issue around the error message “Could not execute sp_replcmds”. Today’s replication issue comes from an error message that greeted me this morning:
“Cannot insert explicit value for identity column in table [table] when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544”
Although no changes had occurred last night, it seemed that a table that had been published sometime ago was empty until part of the application inserted into the table and the commands had attempted to be distributed out. Typically, and identity columns should be marked as “NOT FOR REPLICATION” in the create table script:
CREATE TABLE authors ( COL1 INT IDENTITY (1, 1) NOT FOR REPLICATION PRIMARY KEY )
So I scripted out the affected table on the publisher database and was surprised to see that this was already set. So I double checked the subscriber databases and was equally surprised to see that “NOT FOR REPLICATION” was NOT set. It’s difficult to ascertain why this was the case; maybe someone had added it to the publication without the “NOT FOR REPLICATION” setting, created a snapshot/re-initialised and then realised their mistake and update the publisher, not realising that this change would not be pushed out. The easiest way to change this setting is to run the following stored procedure:
declare @int int set @int =object_id('authors') EXEC sys.sp_identitycolumnforreplication @int, 1
But this stored procedure needs to be run at both the publisher database AND the subscriber database. So I ran the stored proc on the subscribers and checked the distribution log for each subscriber and the error was resolved: there’s no need to send out snapshot or reinitialise the publication.
The stored procedure can also be used to mark a tables identity columns for replication, by specifying 0 instead of 1.
I wonder if replication can make it three surprises in a row? Come back tomorrow to find out!Follow @rPh0enix