12 August 2009

Replicating Stored Procedures

For occasionally connected client replication, we are publishing stored procedures so all the clients have what they need to interact with their local data. So, what happens when you need to modify a procedure? Let's just alter one and see what happens. Ready? ALTER PROCEDURE usp… Now, let's wait and see what happens at the client... okay, nothing yet. Let's check again; still nothing. Okay, we'll wait a few minutes. Now? Still nothing. What's going on? Let's ask BOL.

Me: Hey, BOL, what happened to my stored procedure definition?

BOL: Well, the ability of merge replication to replicate stored procedure definitions (or views or user-defined functions), is merely a convenient mechanism for deploying these components to subscribers.

Me: What, exactly does that mean?

BOL: Subsequent changes to the definitions of these objects are not copied automatically to Subscribers.

Me: Thanks, BOL. You sure know your stuff.

So, there you have it. If you use replication to send procedures to your clients, it only works once. After that, you're on your own. Good thing we learned how to use sp_addScriptExec last time!

No comments: