Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|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
add comment

1 Answer

up vote 2 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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