Microsoft KB Archive/324361

= PRB: Automatic Identity Range Handling Is Not Correct If the Merge Agent Runs an Insert Trigger =

Article ID: 324361

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q324361



SYMPTOMS
If a SQL Server 2000 replication Merge Agent invokes a trigger that in turn inserts into a table that uses automatic identity range handling, incorrect values can be set for the identity column.



RESOLUTION
Avoid the use of triggers to insert data into tables that are set to use automatic identity range handling. Also, if a Merge Agent is not going to run the trigger, you can create the trigger by using the Not For Replication option.



STATUS
This behavior is by design.



MORE INFORMATION
Automatic identity range handling helps manage identities across replicas in a merge replication system. This feature is enabled when you create the publication. A table that is part of a merge publication that has this feature enabled expects explicit values to be provided for the identity column whenever a Merge Agent performs an insert because the Not For Replication constraint is automatically enabled on the identity column when you use automatic identity range handling. But if the Merge Agent invokes a trigger which in turn performs the insert, the trigger might not provide explicit values for the identity column, which causes an incorrect value to be set for this column.

Steps to Reproduce the Behavior
  Create two tables (t1 and t2) on the publisher, and then create a trigger on table t1: create table t1(c1 int identity(1,1) primary key, c2 varchar(20),c3 uniqueidentifier rowguidcol) create table t2(c1 int identity(1,1) primary key, c2 varchar(20),c3 uniqueidentifier rowguidcol) go create trigger Table1InsUpdTrigger on t1 for insert, update as declare @col1          int, @col2         char(10)

declare Table1Cursor cursor for select     c1,c2 from inserted

open Table1Cursor

fetch next from Table1Cursor into @col1, @col2

while @@FETCH_STATUS = 0 begin if (@col2 = 'S1') begin insert into t2(c2,c3) values ('S111',newid) end

fetch next from Table1Cursor INTO @col1, @col2

end

close Table1Cursor deallocate Table1Cursor go  Publish tables t1 and t2 while using automatic identity range handling with ranges 1000 for publisher and subscriber, and then run the Merge Agent to apply the initial snapshot. Make sure that the subscriber table t1 does not have the trigger Table1InsUpdTrigger after the Merge Agent applies the snapshot.  Make an update at the subscriber: insert into t1 (c2,c3) values('s1',newid) go  Run the Merge Agent to synchronize the publication and you see that on the subscriber, the column t2 (identity column) has a value of 2009058193 rather than its identity range. The value 2009058193 is only an example, which varies based on your environment.

If changes are made on the publisher side directly, the identity range is maintained for table t2, but it does not work if it is inserted by a trigger that fired because of a Merge Agent.</ol>

Keywords: kbprb KB324361

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.