Search code examples
sql-serverdatabaset-sqlidentitydbcc

"String or binary data would be truncated" on DBCC CHECKIDENT

I am getting a "String or binary data would be truncated" error when i try to reset the Identity.

Declare @tableNameWithSchema varchar(max)
SET  @tableNameWithSchema = '[' + @table_schema + '].[' + @tablenamee + ']'
DBCC CHECKIDENT (@tableNameWithSchema, RESEED, 0)

Can you help me with this ?

Solution

  • By trial and error method I found out the issue: DBCC CHECKIDENT does not take a varchar(max). It should be varchar(100). Although I do not understand why MAX is wrong. If anyone knows why please let us know.

    Following code worked:

    declare @tableNameWithSchema varchar(100)
SET  @tableNameWithSchema = '[' + @table_schema + '].[' + @tablenamee + ']'
DBCC CHECKIDENT (@tableNameWithSchema, RESEED, 0)

    I am keeping this post so that it might be useful for someone else.