Transactional replication, identity and NOT FOR REPLICATION

Ouch.

Here's the scenario. Have a database server (call it DBS-A, DB1) transactionally replicating to another server (DBS-B, DB1). The tables have identities on them as the unique index (to save space, and as DBS-B is readonly have no problems with duplicate data). All works fine.

So now have another server (DBS-C, DB2) replicating data into DBS-A (into another database DB2) using transactional replication. However, the replication is non standard, in that the sp_MSins_xxxx routine has been amended. This change calls a stored procedure which copies data in the DBS-A, DB1 database. This is a SELECT..INSERT type of affair which works perfectly when called from SSMS or via an application.

However, SQL Server thinks that this data is being created by replication, so obeys the NOT FOR REPLICATION flag on the IDENTITY field, and subsequently returns a not null error, expecting a value etc.

What is annoying is that when you add a table into a replication article, it automatically adds NOT FOR REPLICATION onto any IDENTITY fields and I can not see how to remove this via the UI.

After some investigation I found the following system stored procedure which will do this for you:

DECLARE @tableID INT


SELECT @tableID = object_id('myTable')
EXEC sys.sp_identitycolumnforreplication @tableID, 0

Nice !


Problem 2...
Now this seems to work perfectly on empty tables, however to my horror I found that when I tried it on a populated table (over 1million rows) that the log reader blew up with :

The process could not execute 'sp_replcmds' on 'mydatabase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)

Get help: http://help/MSSQL_REPL20011

The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {000017d6:000016d7:00b9}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
Get help: http://help/18805

The process could not execute 'sp_replcmds' on 'mydatabase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

Arrrrgggh !
This was traced to be definitely the table that I changed, as the error disappeared once the article was dropped.

After struggling through dropping and recreating the replication several times, the (unconfirmed as I don't want to try it again) solution appeared to be an order thing:

• Create an empty table with the same schema as the one you want to add to the replication
• Add empty table to replication
• Remove NOT FOR PUBLICATION using above stored procedure call
• Populate the data from full table to empty table
• Move any foreign keys over from referencing tables

That seems to work.