asked on

Replicate Identity Column in MSSQL 2005

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?

ASKER CERTIFIED SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dev_yinz

ASKER

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.

dev_yinz

ASKER

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?

dev_yinz

ASKER

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