0

I'm using the Table Value constructor to insert a bunch of rows at a time.

However if i'm using sql replication, I run into a range check constraint on the publisher on my id column managed automatically.

The reason is the fact that the id range doesn't seem to be increased during an insert of several values, meaning that the max id is reached before the actual range expansion could occur (or the id threshold).

It looks like this problem for which the solution is either running the merge agent or run the sp_adjustpublisheridentityrange stored procedure.

I'm litteraly doing something like :

INSERT INTO dbo.MyProducts (Name, ListPrice)
VALUES ('Helmet', 25.50),
       ('Wheel', 30.00),
       ((SELECT Name FROM Production.Product WHERE ProductID = 720),
        (SELECT ListPrice FROM Production.Product WHERE ProductID = 720));
GO

What are my options (if I don't want or can't adopt any of the proposed solution) ? Expand the range ? Decrease the threshold ?

Can I programmatically modify my request to circumvent this problem ?

thanks.

LB40
  • 103
  • 4

1 Answers1

1

What are my options (if I don't want or can't adopt any of the proposed solution) ? Expand the range ?

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. You will likely need to increase the range.

Brandon Williams
  • 305
  • 1
  • 7
  • Is this the only option ? this is kind of weird...Moreover, I don't know by how much I should increase the range... – LB40 Mar 22 '12 at 08:02
  • You can either increase the range or execute sp_adjustpublisheridentityrange just before doing the bulk insert. Instructions on increasing the range can be found here: http://msdn.microsoft.com/en-us/library/ms151736%28v=sql.105%29.aspx – Brandon Williams Mar 22 '12 at 16:52