Identity range check conflict when inserting a table constructed value....
-
14 марта 2012 г. 12:16
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
- Изменено Laurent Burgy 14 марта 2012 г. 14:22
Все ответы
-
14 марта 2012 г. 14:12Модератор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
-
14 марта 2012 г. 14:22
I've edited my question, I was wrong, the problem is more about Table constructed values inserted.
-
14 марта 2012 г. 16:45Модератор
...(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. -
14 марта 2012 г. 16:47Модератор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
-
14 марта 2012 г. 17:00
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.
.png)
