Snapshot Replication and Identity Column

Question

 Hi, I have a table that needs to be exported to another server every night. I set up snapshot replication to do this. The table has an identity column. To my surprise this column never gets replicated to subscriber. It has NULL values in it. rest of the data is fine.  While creating replication, in sp_addarticle I put 'Manual' option for @identityrangemanagementoption and not for replication. Am I doing it wrong? the table has millions of records. I have 2 questions.

1. What is the best way to populate that identity column temporarily?

2. How can I overcome this in snapshot replication?

Can someone please advise me?

Thank you very much

Answer 1

I belive when setting up replication, within the stored procedure there is a parameter which allows you to manage identity columns. See this link http://msdn.microsoft.com/en-us/library/ms152543(v=sql.105).aspx

and pay attention to the section called Specifying an Identity Range Management Option

Answer 2

SQLmaddy,

I was able to use Snapshot Replication and replicate identity values using manual identity range management and with the identity column marked NFR.  I'm unsure why it isn't working for you.  Maybe it is the article schema option?

Can you post your sp_addarticle script here?

---

Brandon Williams (blog | linkedin)

Answer 3

"What is the best way to populate that identity column temporarily?"

Identity Insert may not work on a table involved in replication, but thats the only choice I can think of aside from removing the identity property and inserting manually.

SET IDENTITY_INSERT (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms188059.aspx

Answer 4

Please post the sp_addarticle part of the script you are using using.

You can observe the command being issued using the DMV sys.dm_exec_requests or set up a trace to capture the command.

---

Yaniv Etrogi
site | blog | linked in | mail
Please click the Mark as Answer button if a post solves your problem! or Vote As Helpful