CODEWITHSUNDEEP
sql-serverdatabase
Austin Haws
Austin Haws

Reputation: 384

SQL Server: checkident: "[S00014][2560] Parameter 3 is incorrect for this DBCC statement."

Using an SQL Server database. A lookup table is having 50 new items added to it using a database versioning tool so the same script will be run on all three databases. There is a Dev, QA, and Prod version of the database and because of political reasons prod can't be cloned to the other environments. The table has an autoincremented ID column and another table of child records. In order to tie the children to the new parents the autoincremented id sequence must be repeatable and predictable. At this point it can't be switched to a sequence.

DBCC checkident ('inspectionitem', reseed, 9990)

will set the autosequence's next number so that new items will get a predictable id. The maximum ID among all three databases for the current ID fields is about 5000 so for the script, just bumping it to 10000 is fairly guaranteed to not be a problem. The autoincrement is stepping by 10 each time so to get the first record to be ID 10000, had to set the seed to 9990.

When setting the seed to 9990, SQL Server gets the error:

[S00014][2560] Parameter 3 is incorrect for this DBCC statement.

When setting the seed to 9991->10000 it works, but setting the seed to 9990 gets the error.

I'll try a different starting place and some other workarounds, but why does 9990 get this error?

Thanks

Upvotes: 2

Views: 5564

Answers (2)

Abdul
Abdul

Reputation: 2120

I had a same issue and when I checked my table. There was no record in it.

Also, check if identity is ON for the table.

Upvotes: 0

Austin Haws
Austin Haws

Reputation: 384

https://msdn.microsoft.com/en-us/library/ms187745.aspx

Turns out the ID field was a smallint which can only go to 32767. It was overflowing.

Upvotes: 1

Related Questions