Indentity Insert Problem with Trans Rep MSSQL2008R2
I have a client with transactional replication set up (no merge or updateable subscriber) on a sql2008r2 instance (10.50.4279) in a staging environment that is failing when an update occurs on table that has a trigger that subsequently inserts data into another table with an identity column. Replication fails with: 42000 Explicit value must be specified for identity column in table 'sometable' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. 545 I have found kb articles describing this as a problem in 2000 but have found nothing about it in 2008. Also the same version of sql, same OS, same physical server arrangement, same code base, etc. exists in production and production is not having the problem. The subscriber's copy of the table has NOT FOR REPLICATION. The subscriber's trigger does not. (may be the issue) Does anyone have a suggestion as to what might be different between the environments so I have a starting point as to where to look? Again production environment is fine but staging is failing.