Arithmetic overflow error converting IDENTITY to data type int

Version 8

    Verified Product Versions

    Endpoint Manager 9.6

    Problem


    An error log shows an exception along the following lines:

    Arithmetic overflow error converting IDENTITY to data type int

     

    Examples

     

    When running createLandeskrights.exe the following error appears:

    "03/10/2014 09:35:58 INFO  2836:1    RollingLog : Exception in UpgradeUserGroupCache: Arithmetic overflow occurred.

    Arithmetic overflow error converting IDENTITY to data type int. at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) "

     

    When an inventory scan files is processed on the core, the SCN files is moved to the ErrorScan Folder and the following error appears in the Windows Application Event Log:

    Error comitting on table FILEINFOINSTANCE:   ARITHMETIC OVERFLOW ERROR CONVERTING IDENTITY TO DATA TYPE INT.               Increased column size might be necessary.

     

    When rolling up certain tables dont get rolled up correctly or partley , for example the PATCHHISTORY table does not update correctly.

     

    This error can happen on any DB table. This can effect both the LDMS core and the RollUp Core

     

    Cause

     

    This issue is not caused by a limitation or a defect from the LDMS console or core but actaully a limitation from the Microsoft SQL server.


    SQL Error received: Arithmetic overflow error converting IDENTITY to data type int:

     

    Please see this article form TechNet on the issue:

    http://blogs.msdn.com/b/rathomas/archive/2012/11/06/business-contact-manager-2010-arithmetic-overflow-error-converting-identity-to-data-type-int.aspx

     

    Solution


    To reseed the table:

     

    For Example for the PatchHistory table issue the solution is as follows


    This is the SQL statement to run on your rollup core to resolve the issue that you are having.

    It is higly recommended that you perform a full backup of the LANDESK database before running the SQL query.

     

    USE LDMS95RollUp
    DELETE FROM [dbo].[PATCHHISTORY]
    DBCC CHECKIDENT ("PATCHHISTORY", reseed, 1)
    GO
    

     

    Change "LDMS95RollUp" to the name of your roll up core DB name.

    Change "PATCHHISTORY" to the name of the table with the error

     

     

    For the other issue with the users not be created or updated due to this limitation this is a lot more complicated as the DB table for these users , [dbo].[ConsoleUser] is connted and referenced in so manay other tables. This goes for other tables that have this error and are conneted to a variety of other tables.

     

    Deleteing and reseeding these tables would be a massive issue and would corrupt your DB emmensley . So instead you can restet the counter in the effected table to a higher number than the last row and start the whole process of again.

     

    To do this follw these steps:


    1.To find the maximum ID for the table with this error run this SQL query :


     

    Select max(ID) from table


     

    Where ID = primary Key

    And Table = the name of the table with the error

     

    For example for the console user table the primary key is: ConsoleUser_IDN  and the table is : ConsoleUser

    Example query:


     

    Select max(ConsoleUser_IDN) from ConsoleUser

     

     

    This will return a very large number . Using this number run the following query to reset the counter and continue to use your DB

     

     

    DBCC CHECKIDENT ("Table", reseed, x)

     

     

    Table = the table with the error

    X = The number from the abover query plus 10. !0 is used as a buffer so you can easily differentaite between the DB entries now and after you have  reseeded the table