Select @@identity in Merge Replication I'm going to implement database replication (merge publication, bi-direction) between two servers running SQL Server 2008 Standard edition. The setup is alright but it comes to a problem when synchronizing data.

The main task of my program is to let user register and after the INSERT statement is executed on my member table (with uid primary key and auto-incremented), it queries SELECT @@identity; for the max. primary key. The program then uses this key for insertion into another table.

All these work fine without replication.

But after replication, when doing SELECT @@identity; it returns back an incorrect primary key. (Suppose now member contains 100 members, after inserting a new record, it should have returned 101. But instead it returns 2 or 3 which is wrong and not what I expected.)

So my question is how to get a correct value using SELECT @@identity;.

I know the possible solution is SCOPE_IDENTITY(), but I want to know how to fix this without changing the source code.

Thanks in advance.
@@identity is not a good solution and will return incorrect results since .  SCOPE_IDENTITY() is session based and will return the last inserted record for the session.  There isn't a way to correct this without a code change.