viernes, 27 de julio de 2012 10:31
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
Todas las respuestas
viernes, 27 de julio de 2012 14:20
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
viernes, 27 de julio de 2012 17:13Moderador
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?
viernes, 27 de julio de 2012 23:20
"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
domingo, 29 de julio de 2012 7:40
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.