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.
SCOPE_IDENTITY()
to make sure it corrects the issue, then use that. There's no way to fix something with bad code without rewriting the code, and@@IDENTITY
is loaded with problems - that's why they madeSCOPE_IDENTITY()
in the first place.