MSSQL 2000 replication, incrementing an integer column on a single row using an update statement

Question

I have 2 web servers, both of which connect to separate mssql 2000 database servers, these two servers use merge replication in order to collate tracking data.

Now each web server runs an update statement to its database which goes something like this:

"update trackingdata set trackingvalue=trackingvalue+1 where id=@id"

Now I would have expected that when i go to each web server and action this that there would be a value on 1 in each table and then when the merge replication runs i would have a value of 2 in each table as there was 2 update statements but when the merge runs i am being left with a value of 1.

Can anyone explain why this is and or if I am doing something wrong ?

I need the value to increment on each server and for the new updates to add together on a merge.

Thanks in advance for any help you can provide guys!

Cheers. Dylan.

Update: I think I should be using the 'Additive conflict resolver' for the article in question however I have supplied the column name in the text box below but I am receiving an error The specified conflict resolution column 'trackingvalue' could not be found., does anyone know how to get Additive conflict resolution working ?

Answer 1

You must either define identity ranges for each server (subscriber), but unsure if this was possible with SQL Server 2000. A more simple approach is to use GUID/uniqueidentifers for IDs, then you avoid conflicts.