STDLOG error:[ SQL Code=8115 SQL State=22003] Arithmetic overflow error converting IDENTITY to data type int
Article ID: 257441
Updated On:
Products
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
Feedback
