Our nightly SCCM _inv_raw_arp import will not run. The error log displays this error:
[SQL Server Native Client 11.0][SQL Server] Arithmetic overflow error converting IDENTITY to data type int.
Complete error log message: asp_db_ps->execute(): failed. error: failed for query: INSERT INTO inv_raw_arp WITH (TABLOCK) (time, import_id, data_source_id, publisher, product, product_version, device_id, package_id, installation_date, tracking_date, tracking_quantity, demand_org_level_2_id, instance_name)
SELECT CONVERT(NVARCHAR(19), GETDATE(), 120), ext_import_id, data_source_id, ext_publisher, ext_product, ext_product_version, device_id, ext_package_id, ext_installation_date, ext_tracking_date, ext_tracking_quantity, demand_org_level_2_id, ext_instance_name
WHERE status <> 'error' AND (inv_raw_arp_ib.data_source_id IN (5)) . error: asp_db_mssql_sqlsrv->execute_prepared(): failed. error: sqlsrv_execute() failed: #1: SQLSTATE: 22003; code: 8115; message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Arithmetic overflow error converting IDENTITY to data type int.
#2: SQLSTATE: 01000; code: 3606; message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Arithmetic overflow occurred.
This error may occur in any SQL Server environment, where a table has repeated inserts and deletes; however the deleted rows are not reclaimed by SQL Server. For a full explanation, see the following article: http://stackoverflow.com/questions/2295731/maximum-for-autoincremental-int-primary-key-in-sqlserver
If you are using a CA SAM 3.x release, follow these steps:
In SQL Server Management Studio, login as a user with administrator authority and run the following SQL:
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 example, the following query fails on the inv_raw_arp table: asp_db_ps->execute(): failed. error: failed for query: INSERT INTO inv_raw_arp WITH (TABLOCK)
So, the query would be: DBCC CHECKIDENT(inv_raw_arp, RESEED,0)
If you are using CA SAM 4.x releases, then upgrade to the latest CA SAM 4.x.x release.
This problem was resolved in CA SAM 4.2.2 and above.
After upgrading to SAM 4.2.2 or above, you may also need to run the SQL Server command: 'truncate table inv_raw_arp', once, after the upgrade to clear the table. If you experience the 'arithmetic overflow' error on another table, then use the truncate table command on the appropriate table name.