Difference between revisions of "Microsoft KB Archive/175624"

From BetaArchive Wiki
m (Text replacement - "<" to "<")
m (Text replacement - ">" to ">")
Line 49: Line 49:
 
<div class="errormessage">
 
<div class="errormessage">
  
Attempting to insert explicit value for identity column in <tablename&gt; when identity_insert is set to off.
+
Attempting to insert explicit value for identity column in <tablename> when identity_insert is set to off.
  
 
</div>
 
</div>
Line 90: Line 90:
 
In the normal case, when either col1 or col2 for a particular row has a non- NULL value, the SQL statement that would go into SQL Server would be:<br />
 
In the normal case, when either col1 or col2 for a particular row has a non- NULL value, the SQL statement that would go into SQL Server would be:<br />
  
<pre class="codesample">  Insert into <owner&gt;.TABLE_D (colB, colC) values (val1,val2)
+
<pre class="codesample">  Insert into <owner>.TABLE_D (colB, colC) values (val1,val2)
 
                 </pre>
 
                 </pre>
 
<br />
 
<br />
 
When both col1 and col2 are null for a particular row, the SQL statement that goes into SQL Server would include all the columns in the destination table, thereby trying to insert NULL values into every column, as in the following:<br />
 
When both col1 and col2 are null for a particular row, the SQL statement that goes into SQL Server would include all the columns in the destination table, thereby trying to insert NULL values into every column, as in the following:<br />
  
<pre class="codesample">  Insert into <owner&gt;.TABLE_D (colA,colB,colC,colD) values
+
<pre class="codesample">  Insert into <owner>.TABLE_D (colA,colB,colC,colD) values
 
   (NULL,NULL,NULL,NULL)
 
   (NULL,NULL,NULL,NULL)
 
                 </pre>
 
                 </pre>
Line 105: Line 105:
 
<div class="errormessage">
 
<div class="errormessage">
  
Attempting to insert explicit value for identity column in <tablename&gt;<br />
+
Attempting to insert explicit value for identity column in <tablename><br />
 
when identity_insert is set to off.
 
when identity_insert is set to off.
  

Revision as of 09:34, 21 July 2020

Knowledge Base


PRB: Error Attempting to Insert Explicit Values with an Append Query

Article ID: 175624

Article Last Modified on 10/16/2003



APPLIES TO

  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition



This article was previously published under Q175624

SYMPTOMS

An append query from a Microsoft Access 97, Access 95, or Access 2.0 database to a table in SQL Server 6.0 or SQL Server 6.5 may generate the following message:

Attempting to insert explicit value for identity column in <tablename> when identity_insert is set to off.


This message appears if both of the following conditions are true:

  • The append query tries to insert a record from the source table that has NULLs for all the columns, or the selected data from the source table has rows that have NULLs for all the columns.


-and-

  • The destination table in the SQL Server database has a column with the IDENTITY property.

Note that this problem occurs even if explicit values for the identity column are not specified.

CAUSE

Jet handles the append query request in a different way for rows when all the columns contain NULL values. For an example, see the MORE INFORMATION section of this article.

WORKAROUND

To work around this problem, make sure that the rows from the source table being inserted into the destination table do not have all NULL values. You can do this by adding the criteria "IS NOT NULL" for any particular column in the append query.

MORE INFORMATION

Suppose you have the following two tables:

Source table : TABLE_S with columns col1, col2, col3 allowing NULLs.
Destination table: TABLE_D with columns colA, colB, colC, colD with colA being an IDENTITY column and the rest allowing null values.

Col1 and col2 from TABLE_S are being selected and appended to TABLE_D with colB and colC as the corresponding columns.

In the normal case, when either col1 or col2 for a particular row has a non- NULL value, the SQL statement that would go into SQL Server would be:

   Insert into <owner>.TABLE_D (colB, colC) values (val1,val2)
                


When both col1 and col2 are null for a particular row, the SQL statement that goes into SQL Server would include all the columns in the destination table, thereby trying to insert NULL values into every column, as in the following:

   Insert into <owner>.TABLE_D (colA,colB,colC,colD) values
   (NULL,NULL,NULL,NULL)
                


This happens even though colA and colD are not specified in the append query.

Because colA in TABLE_D has an IDENTITY property, SQL Server returns the following error message and the append query fails:

Attempting to insert explicit value for identity column in <tablename>
when identity_insert is set to off.


Additional query words: col

Keywords: kbinterop kbprb KB175624