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