Microsoft KB Archive/167867

= BUG: SQL-DMO BulkCopy Object IncludeIdentityValues Property Fail =

Article ID: 167867

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q167867



BUG #: 1319 (Starfighter65)



SYMPTOMS
When inserting into a SQL Server table using the SQL Server Distributed Management Object's (SQL-DMO's) BulkCopy object, automatically incremented values are inserted into the table's identity column based on the identity column's seed. The values in the source file are not inserted into the identity column. This occurs even when BulkCopy's IncludeIdentityValues property is set to True.



STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.



MORE INFORMATION
The BulkCopy object's IncludeIdentityValues property activates IDENTITY_INSERT so that a source file's values that map to an identity column in the destination table can be explicitly inserted into the identity column of a table. Records import properly, but the literal values in the source file are not inserted into the identity column; rather, new, incremented values, based on the initial property column's seed, are inserted.

SQLTrace reveals that IncludeIdentityValues property runs a SET IDENTITY_INSERT ON in a different connection to SQL Server than the connection that runs the ImportData method. The SET command only applies to the current batch or connection.

For example, suppose you have a table with an identity column called Id, whose seed is 1, and a char column called MyChar:

  Id   MyChar --  --   1     A   2     B

Setting the IncludeIdentityValues to True should import the following data

  Id   MyChar --     100   H   101   I

into the SQL table like so:

  Id   MyChar --  --   1     A   2     B   100   H   101   I

However, it fails to work properly, and actually inserts the data as:

  Id   MyChar --  --   1     A   2     B   3     H   4     I

