SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question of the Day for 13 Oct 2004


Question of the Day for 13 Oct 2004

Author
Message
SW21_Bob
SW21_Bob
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 55

OK. I'm confused. The question says the error is because of a duplicate primary key in the publisher, but all of the answers deal with the subscriber or agent. It seems to me the real problem is the duplicate in the publisher and it should be fixed there.




Bob
SuccessWare Software
DevinLee
DevinLee
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 47
I think the implication is that the record being inserted is a duplicate of one already on the subscriber. Probabaly because the subscriber was updated by something other than the distributor, and they are not using bidirectional replication.



kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3423 Visits: 1323

I'm unfamiliar with transactional publication. What insert stored procedure is being referred to in answer D? Is this something automatically generated by SQL?

-Kevin





DevinLee
DevinLee
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 47

When using transactional replication, you have the option of using a custom stored procedure instead of a standard insert at the subscriber. This stored proc would run instead of the regular insert. The same is true for updates and deletes. The default behavior is to use the regular insert, update, and delete. No stored proc is automatically generated.

Devin





nap_parikh
nap_parikh
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 2
In transactional repliction When you register a subscriber and run the snap shot agent at the first time, it will create 3 store procedures for each table for insert, update and delete. You can either use those standard store procedures or you can customised those procedures.



DevinLee
DevinLee
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 47

Whoops, I stand corrected. SQL Server does create stored procs by default. But I could only find stored procs for initial population, conflict resolution, insert, select, and update. I didn't see any delete ones, oddly enough.





Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14459 Visits: 12228
The most common cause of this error, in my experience, is that someone has failed to mark a trigger "not for replication" that needed to be so marked. The correct long term solution is to fix the trigger, and then reinitialise and restart the subscription.

Doing all four things recommended here serious risks leading to catastrophic error - two of them just say "I don't care if it's wrong, just get on with it", which is normally considered completely unacceptable (it's as bad as running all your queries with hordes of NOLOCK hints). If someone is updating a subscriber which is not handing stuff back to the publisher, that probably needs serious management action to ensure that it doesn't happen again, not just deleting a row on the subscriber. So three of the four things are just plain wrong.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search