Sunday, February 12, 2012

Can't Update Identity Column in Transactional rep.

Hi,
I have Transactional replication setup where at PUBLISHER 90% of my tables
have PK Auto Increment. Which replicated to SUBSCRIBER.
Publisher(Publishing database) get updated by Importer(In-House tool)
Subcriber get updated by Publisher as well from Website and needs to have PK
Auto Incremental enable.
So what have done is to set up different kind of range at Publisher (odd
number) and Subscriber(even number) for Identity Seed.
Also have modify the SP (sp_MSins) with SET IDENTITY_INSERT TABLE ON.
But I am keep getting error that can't update Identity column X and
reference to sp_MSUpd_
Could any one please let me know why I am getting this error, as SET
IDENTITY_INSERT TABLE ON is only when you inserting records not updating
records?
Shan
This is usually a compilation error from the stored procedure. Do you have
the identity attribute existing on the subscriber tables? If so, it
shouldn't be there for vanilla transactional replication as the identity
values are maintained by the publisher only. For queued updating subscribers
the identity property exists, but the stored procedures have a modified
syntax.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

No comments:

Post a Comment