SQL error of -8115 Arithmetic overflow error converting IDENTITY to data type decimal

Question

A column(USER_MSG_SEQ_NUM) is declared with the property IDENTITY with the datatype as DECIMAL (05,00) for one of the table. When the maximum value for this field reaches 99999, MS-Sql doesn’t have an option(automatic) to reset the value to 1 and so it abends with the error message as "Arithmetic overflow error converting IDENTITY to data type decimal ". Below are field declaration in the table. SEQ_NUM      DECIMAL (05, 00) NOT NULL IDENTITY (1, 1). Need inputs to fix this issue by resetting the value to 1 similar to the option we have with DB2 database MS-SQL version we use in the application is 5.16.3

Answer 1

You could change your logic to use sequences in SQL Server too. Sequences can cycle and you can use their NEXT VALUE as a DEFAULT of a column in the table definition. https://learn.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver16

Answer 2

You can use DBCC CHECKIDENT (Transact-SQL) with the RESEED option. Of course it will fail again, when die IDENITY reaches an existing value. At all, that sounds like a strange database/table design.