x

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.

more ▼

asked Mar 21, 2014 at 06:11 PM in Default

avatar image

mbryce
31 1 3 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Problem resolved...triggers needed disabled on subscriber.

more ▼

answered Mar 27, 2014 at 12:45 PM

avatar image

mbryce
31 1 3 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x378
x149
x35

asked: Mar 21, 2014 at 06:11 PM

Seen: 549 times

Last Updated: Mar 27, 2014 at 12:45 PM

Copyright 2017 Redgate Software. Privacy Policy