2

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.

| improve this question | |
  • 1
    I think you need to test 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 made SCOPE_IDENTITY() in the first place. – JNK Sep 17 '13 at 10:05
  • 4
    @@Identity should not be used. You should switch to using OUTPUT (scope_ideinty() works but has problems if parralleism is used, OUTPUT works in all conditions) and yes that requires code changes. – HLGEM Sep 17 '13 at 13:59
2

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.