BUG: SQL-DMO BulkCopy Object IncludeIdentityValues Property Fail

ID: Q167867

The information in this article applies to:

Microsoft SQL Server version 6.5

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 <tablename> 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

REFERENCES

For more information about the BulkCopy object's IncludeIdentityValues property, query on "IncludeIdentityValues" in SQL Server Books Online.

For sample code using the BulkCopy object, see the following article in the Microsoft Knowledge Base:

Q152801 : INF: Examples of Sp_OA Procedure Use and SQLOLE.Transfer Object

Additional query words: error message DMO

Keywords : kbenv kbhw SSrvAdmin SSrvDMO SSrvErr_Log SSrvGen kbbug6.50
Version : 6.5
Platform : WINDOWS
Issue type : kbbug