STDLOG error:[ SQL Code=8115 SQL State=22003] Arithmetic overflow error converting IDENTITY to data type int
search cancel

STDLOG error:[ SQL Code=8115 SQL State=22003] Arithmetic overflow error converting IDENTITY to data type int

book

Article ID: 257441

calendar_today

Updated On:

Products

CA Service Desk Manager CA Service Management - Service Desk Manager

Issue/Introduction

End users are unable to access the CA SDM application and errors similar to the following are seen in the SDM STDLOGs:

EXAMPLE-SDM-SERVER bpvirtdb_srvr          11656 ERROR        vdbmisc.c              688 Miscellaneous Database error occured :[Microsoft SQL Server Native Client 11.0] [ SQL Code=3606 SQL State=01000] Arithmetic overflow occurred.; [Microsoft SQL Server Native Client 11.0] [ SQL Code=8115 SQL State=22003] Arithmetic overflow error converting IDENTITY to data type int.

EXAMPLE-SDM-SERVER animator_nxd            4128 ERROR        animator_nxd.c        1839 AHD04199:An unexpected Database error occurred. Contact your administrator. in animator ANI:698053493 (rest_access:2335167) for atev:669065408 in method examine_lock_list

EXAMPLE-SDM-SERVER sqlagt:Procedure0       1568 ERROR        sqlclass.c            1016 SQL Execute  failed: [Microsoft SQL Server Native Client 11.0] [ SQL Code=3606 SQL State=01000] Arithmetic overflow occurred.; [Microsoft SQL Server Native Client 11.0] [ SQL Code=8115 SQL State=22003] Arithmetic overflow error converting IDENTITY to data type int.

EXAMPLE-SDM-SERVER sqlagt:Procedure0       1568 ERROR        sqlclass.c            1017 Clause ({ ? = call dbo.usp_lock_acquire;1( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }) Input (<string>Animator|<string>698053498|<int>18622|<string>domsrvr|<uuid>(NULL)|<string>superuser|<string>ANI|<string>rest_access:2335168|<int>1673328943|<int>630)

EXAMPLE-SDM-SERVER sqlagt:Procedure0       1568 ERROR        sqlsrvr.c             2616 Execution failed. MISC_DB_ERROR: Clause ({ ? = call dbo.usp_lock_acquire;1( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }) Input (<string>Animator|<string>698053498|<int>18622|<string>domsrvr|<uuid>(NULL)|<string>superuser|<string>ANI|<string>rest_access:2335168|<int>1673328943|<int>630)

Environment

CA Service Desk Manager 17.3 or higher

SQL MDB

Cause

This error can occur in any SQL Server environment, where a table has repeated inserts and deletes; however the deleted rows are not reclaimed by SQL Server.

Resolution

Arithmetic overflows occur when a data value exceeds the datatype of the column it is going into.  

In this case, the overflow occurred on an integer IDENTITY column, which means the value exceeded 2147483647

This query will return all database tables with IDENTITY columns, WITH their current IDENTITY values.  

SELECT

   tables.TABLE_NAME,

   IDENT_SEED(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) Seed,

   IDENT_INCR(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) Increment,

   IDENT_CURRENT(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) Current_Identity

FROM

   INFORMATION_SCHEMA.TABLES tables

WHERE

   OBJECTPROPERTY(OBJECT_ID(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME), 'TableHasIdentity') = 1

   AND tables.TABLE_TYPE = 'BASE TABLE';

Sample result:


 

In SQL Server Management Studio, login as a user with SQL administrator authority and run the following SQL command:

DBCC CHECKIDENT(TableName, RESEED,0)

This query reseeds the primary table identity key back to 1 again, where TableName equals the actual table name that failed on the INSERT.

For the above example, the query would be:

DBCC CHECKIDENT (usp_record_lock, RESEED, 0) 

After running this query, recycle the CA SDM service and users should be able to access and use the CA SDM application.
Recycle Service Desk services.

Additional Information