BUG: SQL-DMO BulkCopy Object IncludeIdentityValues Property Fail

• Microsoft SQL Server, version 6.5

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

   Id   MyChar
   --   ----

   100   H
   101   I

   Id   MyChar
   --   ------

   1     A
   2     B
   100   H
   101   I

   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:

   ARTICLE-ID: Q152801
   TITLE     : INF: Examples of Sp_OA Procedure Use and SQLOLE.Transfer
               Object