Problem:

Received the following ODBC error when trying to insert a new record into a SQL Server table with the SQL Insert statement using RPG2SQL.
 
SQL Insert statement:
 
insert into shippers (ShipperID) Values('4')
 
ODBC Error:
 
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'table name' when IDENTITY_INSERT is set to OFF.

Solution:

You need to omit the IDENTITY column from the table INSERT statement.  If the insert is being used as part of a stored procedure, you can then use @@IDENTITY field to get the assigned SQL Server assigned IDENTITY value for subsequent table inserts.

Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: December 10, 2016