Ian Kirk - SQL DBA

It’s obvious I’m still on what has been dubbed the “PASS high” and I certainly hope it doesn’t wear off any time soon.  It’s a great feeling, having so many new ideas and techniques at your fingertips.  Meeting all these new people then realizing how much I’ve been missing and diving in - that’s been a blast.

At the same time, it’s made me reflect on the things happening in my own team.  Here I sit, technically on Thanksgiving Day, on my laptop, winding down from cleaning house, and I get a ping in IM from our DBA in Japan.  It’s late in the afternoon for him and I know he was called in early.  Beyond that, I know this guy is a really sharp DBA and if he’s asking for something, it certainly isn’t trival.  I hesitate slightly, but I respond.

He’s hit a snag with replication - the distributor is complaining about the system generated replication procedures being missing on the target.  Amusingly, I’d spent the plane rides from Nashville to Seattle typing up some internal training materials on replication (which I’m realizing are way too in depth for a start) and I knew exactly what he needed to do.

Unfortunately, I explained it in way too little detail, and he didn’t grab it right away.  He toiled for a few minutes and I checked back in.  I knew from his response that my answer hadn’t made sense.  Suddenly I launched into a paragraph explaination of replication distribution and how to re-generate the procs that were missing.  He plugged away for a minute and came out victorious.  He was extremely thankful, given the time of night, and I told him it was pleasure enough knowing that I’d been able to teach him a bit more about replication.  This is the spirit of PASS - sharing with your fellow DBA as peers.

So - if you didn’t already know, here’s (my take on) the short version:

Replication transfers data uses parametered stored procedures and these stored procedures are system generated by the snapshot agent.  The snapshot agent leverages the system stored procedure sp_scriptpublicationcustomprocs to generate those procedures.  It then applies that script only to the target environment and at a later time the distribution agent, when attempting to transfer data, calls these procedures with the appropriate parameters.

Since the distribution agent is complaining that these specific table’s stored procedures are missing, all you need to do is to run that system stored procedure which will generate a script for you.  That script will have  all of the replication stored procedures for every published table in the publication you indicate.  From that large system generated script, you must locate the 3 procedures associated with the specific table that is creating errors and then run those procs on the target environment.

Those procs, for reference, are the sp_MSins_<tablename>, sp_MSupd_<tablename>, sp_MSdel_<tablename>, for INSERT, UPDATE, DELETE respectfully.  Also, the procedure sp_scriptpublicationcustomprocs needs to be executed in the publishing database and it takes a single parameter of the publication name.

Our little hang up came when we changed the primary key constraint on a published table, but the snapshot agent didn’t run to replace the procs.

If you see anything I’ve stated wrong, feel free to correct me!