On a computer that has Microsoft Data Access Components 2.8 installed, an application receives an incorrect value for the identity column from a SQL Server 2005 database

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:

• Windows Server 2003
• Windows XP
• Microsoft Data Access Components 2.8 (MDAC 2.8)

This problem does not occur in a Microsoft SQL Server 2000 database.

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:Note 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.

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:

• Insertion in Table1
• Insertion in Table2 by the trigger

In this scenario, the @@IDENTITY variable and the SCOPE_IDENTITY function return different values when a row is inserted in Table1. The @@IDENTITY variable returns the last identity column value that is inserted across any scopes in the current session. In this case, the @@IDENTITY variable returns the identity value that is inserted in Table2. However, the SCOPE_IDENTITY function returns the identity value that is inserted in Table1, because the SCOPE_IDENTITY function returns the value that is last inserted in the same scope. The SCOPE_IDENTITY function returns the NULL value if the function is invoked before any INSERT statements that are made to an identity column occur in the scope.

For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base: