Identity range check conflict when inserting a table constructed value....

Question

It seems to be the same problem as :

Consider the following scenario. In SQL Server 2008, you configure a merge publication. You add a table that contains an identity column to the merge publication. Then, you try to perform a batch insert into the table on the publisher. In this scenario, you may receive the following error message on the publisher. 

http://support.microsoft.com/kb/972006

I'm actually using the a table constructed value of a few hundreds records...

SQL Server 2008 introduces the Transact-SQL row constructor (also called a table value constructor) to specify multiple rows in a single INSERT statement. The row constructor consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma. For more information, see Table Value Constructor (Transact-SQL).

but I was wandering if there's any other option ? ...(or should I increase the range so that it could not be reached in a day and run the merge agent every night).

How do you circumvent the problem in the real world :) ?

 

thanks

Answer 1

Do your bcp operation in batches to 10,000 (or whatever your range is), and use the fire_trigger options in your bcp operation.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

I've edited my question, I was wrong, the problem is more about Table constructed values inserted.

Answer 3

...(or should I increase the range so that it could not be reached in a day and run the merge agent every night).

Yes, if you are using automatic identity range management then ensure that the ranges are big enough to accommodate for the number of inserts that may occur between a synchronization.

---

Brandon Williams (blog | linkedin)

Answer 4

Can we see the schema for this table?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 5

Hum, not really I fear. What I can say is that we insert something like 400 rows via one insert, and this is just an identity column auto-incremented.