Cannot insert explicit value for identity column in table 'tablename' when IDENTITY_INSERT is set to OFF

Version 5


    You are getting the above error when running an import, or when saving a record.




    Servicedesk is trying to set a value in a database column which has been set as the Identity column for the table.  Identity columns are supposed to be populated automatically by the database, so when you try to insert data into them you get the above error.  By default, the IDENTITY_INSERT property is set to OFF, and should be kept that way to prevent duplicate IDs.


    In the case of Data Import, this can be caused simply by mapping to the ID attribute of an object.  For example, if you import Articles from an external source that generates its own IDs, and map that attribute across you will get the error.



    SOLUTION 1 - If you are experiencing the error during a Data Import

    Remove the mapping to the object ID attribute, or whatever attribute has been set as the createidentity for the object.


    An important thing to remember when importing into an object that has a createidentity attribute is that Data Import will see it as an unmapped mandatory value, and you will see something like this when you run a Test import:


    empty mandatory value.jpg


    In this instance the error can be safely disregarded because we know the Article ID attribute is going to be populated by the database when a new row is inserted.  Notice that the Start Import button is greyed out, meaning you can't start the import from this dialogue box, so you will need to start the import from the main Data Import screen, or let the Data Import Service run on schedule.


    SOLUTION 2 - If you are experiencing the error when saving a record

    This is because you have a createidentity attribute (eg, Reference Number) on the window that you are displaying.  This is a known issue in 7.6.1, Problem 5767.  If you encounter this issue, please contact your support provider.  The short-term workaround may be to: locate the window that you were viewing via window manager and then remove the createidentity attribute from the window.