Article ID: 940569 - View products that this article applies to.
Consider the following scenario. You create a table that has an identity column in a Microsoft SQL Server 2005 database. You configure a merge replication on the table. An application uses Microsoft ActiveX Data Objects (ADO) to insert a row in the table. Then, the application retrieves the inserted row. However, an incorrect value is returned for the identity column.
This problem occurs when the computer that runs the application has one of the following installed:
The replication process creates insert triggers in the table. When data is inserted in the table, the insert triggers use the @@IDENTITY variable instead of the SCOPE_IDENTITY function. Therefore, the @@IDENTITY variable may return an identity value that is not in the current table.
To resolve this problem, install the hotfix that is described in the following Microsoft Knowledge Base article:
961451Note The hotfix that was described in the "Resolution" section in the earlier version of this article is superseded by the hotfix that is described in 961451.
(http://support.microsoft.com/kb/961451/ )FIX: You receive an incorrect value when you query the last-inserted identity value after you use a client-side cursor to insert data to a table that contains an identity column in an application that uses ActiveX Data Objects
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
The SCOPE_IDENTITY function and the @@IDENTITY variable return the last identity values that are generated in any table in the current session. However, the SCOPE_IDENTITY function returns values that are inserted only within the current scope. The @@IDENTITY variable does not limit the insertion to a specific scope.
For example, assume that the database contains a Table1 and a Table2. Both tables have identity columns. An insert trigger is defined in Table1. When a row is inserted in Table1, the trigger inserts a copy of the row in Table2. This scenario involves the following two scopes:
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/824684/LN/ )Description of the standard terminology that is used to describe Microsoft software updates
Article ID: 940569 - Last Review: August 25, 2009 - Revision: 2.0