Replicate Identity Column in MSSQL 2005

dev_yinz
dev_yinz used Ask the Experts™
on
I am going to replicate a Database in master server to another server for reporting purpose. Both of two servers are using MSSQL 2005 Standard Edt and I use Transaction mode replication. The problem is, there are quite a lot of columns having "Identity" property, which gives the errors when replicate the data.

I am not able to change the Database schema. I could not ignore the replication for those identity columns because they are primary keys in the Database as well. Is it possible to replicate the value of identity column to subscriber? Or is there any script which can disable the "Identity" property for all of the tables in the Database?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
The value of identity column in the master Database must be the same as it is in the slave Database. The replication is one way only, which is from master (publisher) Database replicates to slave (subscriber) Database. And there are only two servers.

Author

Commented:
I run the sp_helparticle procedure and found "identityrangemanagementoption" field has the value 0 and 2. In order to make the consistency of the value of identity columns in publication and subscription Database, should "identityrangemanagementoption" be disabled? If yes, how to disable it?

Author

Commented:
The post describes how to manage the identity column in publication database and subscription database.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial