Problem Note 15020: "ERROR: Can not insert explicit value for identity column in the table" occurs during an update or insert of a DBMS table
 |  |  |  |
When you try to update or insert into a database management system (DBMS) table, by default SAS tries to insert into every available column during an update or insert operation. If the DBMS table contains primary or identity keys, an error similar to the following occurs:
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'CompSales' when IDENTITY_INSERT is set to OFF. ERROR: ROLLBACK issued due to errors for data set ASSESSOR.CompSales.DATA
SAS® 9.1 contains the LIBNAME statement and data set option IGNORE_READ_ONLY_COLUMNS=YES. This option instructs the engine to ignore any read-only columns (such as identity or primary keys) from update or insert operations.
The IGNORE_READ_ONLY_COLUMNS= option is available in the following:
- SAS/ACCESS® Interface to Microsoft SQL Server
- SAS/ACCESS® Interface to ODBC
- SAS/ACCESS® Interface to OLE DB
- SAS/ACCESS® Interface to DB2 under UNIX and PC Hosts
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to ODBC | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M0 | |
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M0 | |||
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M0 | |||
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M0 | |||
| Microsoft Windows XP Professional | 9.1 TS1M0 | |||
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M0 | |||
| Microsoft Windows 2000 Server | 9.1 TS1M0 | |||
| Microsoft Windows 2000 Professional | 9.1 TS1M0 | |||
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M0 | |||
| 64-bit Enabled AIX | 9.1 TS1M0 | |||
"ERROR: Can not insert explicit value for identity column in the table" occurs during an update or insert of a DBMS table. You encounter this issue if the table contains primary or identity keys.
| Type: | Problem Note |
| Priority: | low |
| Topic: | Third Party ==> Information Exchange ==> ODBC (Open Database Connectivity) SAS Reference ==> LIBNAME Engines |
| Date Modified: | 2005-05-06 11:40:33 |
| Date Created: | 2005-04-15 13:32:49 |