I'm trying to figure out why I'm getting this error.  I used my program to insert a record into my production db.  That worked fine.  The transactional replication then copies it to the archive db ok, but immediately after that, I get an error stating that it violated the primary key constraint.  I know this record wasn't in the archive db prior to the replication putting it there.  Why does it try to insert twice?

 My program may be doing the insert and then a post (which is an insert followed by an update if the insert failed, my program catches the error)  Would the transaction that errored out on the production db be transferred down to the archive db?  How can I stop this?  I can not change my programs, I would just hope that I could set the transactional replication set up in a way so that it only transferres successful transactions.

 Thanks in advance,

Ben

One of the KB article refers that :This problem occurs because the INSERT, UPDATE, and DELETE stored procedures do not contain the necessary logic to handle duplicate key violation errors (Transact-SQL error 2601).

When you create a publication and you configure it for queued updating, by default, stored procedures that insert, update, and delete data in the published table are created automatically in the publication database.

These stored procedures are called by the Replication Queue Reader Agent utility to apply transactions at the publisher, to detect conflicts, and to generate compensating commands, which are posted to the distribution database, and then delivered to the subscriber. INSERT, UPDATE, and DELETE stored procedures are created for each article.

In this case confirm the service pack level on the SQL instanes that are involed here.

How do I find the service pack level?  I've done it before, but can't remember the code.  I feel like the instances are all up to date, but I will double check.  You think that the wizard just created poor stored procedures?  I guess I'm still fuzzy with the answer.  I tried the -skiperrors, and it works, but i've read that I should not use that unless I know why, and I don't know why the inserts are getting inserted twice.  Thanks,

Ben

SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'))

Follow what Hemant said above to get the information, in any case for such duplicate rows issue in replication must be avoided in case of identity columns as referred http://msdn2.microsoft.com/en-us/library/ms152543.aspx here.

Thanks for the help.  I have SP4, and i'm not using any identity columns.  Is it true that if you try to insert twice on the production db (first one works, second one fails, but the application catches the error) that it will try to insert twice on the archive db and stop on the error?  It would seem to me that there would be a way to have the log reader agent not distribute commands that errored on the main server.

After doing some research, I think I know the issue, and that is that my replication replaces the delete sp with "NONE", and so when an update happens at the publisher, it passes it down as a delete/insert.  Since the delete is set to do nothing, the insert fails with the 2601 error code.  It looks like I'm going to have to change all my insert stored procedures to first look for the presense of a record, and if it's not there, insert, if it is, update.  Does this seem like the way to go?  I know if will take longer, but I can't think of any other way to get around this.  Thanks,

Ben

it doesn't try to insert it twice... you must remember that replication is by default handling the ranges of your identity fields... if they are not automatically seeded then sql will assign a value to the field automatically... If you wanna find the identity on the subscriber then use (Select @@SCOPE_IDENTITY)...

this will return the value of the inserted row at the subscriber as it will differ from that inserted at the publisher...

 Try setting your seed on identities to 1 so that sql server will handle then automatically, even if this means adding a nonsense int field as a pk....