Tuesday, September 20, 2016

I've Got The SP2 Replication Blues - SP2 For SQL 2014 Changes A System SPROC

I ran into this replication error the other day and I was completely stumped.
Procedure or function sp_MSreplraiserror has too many arguments specified.
 
We started getting that error message shortly after we had applied SP2 for SQL Server 2014 to a server that is a replication Publisher (source of replicated data).

We dug into the commands that were being replicated and found that there were missing rows in the table on the replication Subscriber (destination for replicated data).  Once the rows were populated the errors stopped.  However, after digging in a bit more, we found that this error has an explainable source.

In SP2 for SQL Server 2014, the system SPROC sp_MSreplraiserror has been updated to support an additional parameter for additional error logging detail.  This is good!

When we updated the Publisher with SP2 for SQL Server 2014, the replication SPROCs that actually perform the work (sp_MSdel_*, sp_MSins_*, and sp_MSupd_*) were updated with the additional parameter value to specify when running the sp_MSreplraiserror SPROC.  Unfortunately, the Subscriber (which is where the SPROCs are executed) has not yet had SP2 applied and thus the error is thrown because the system SPROC sp_MSreplraiserror has not yet been updated to support an additional parameter.

To mitigate the issue, I edited the sp_MSdel_* and sp_MSupd_* SPROCs (the sp_MSins_* SPROCs do not execute sp_MSreplraiserror when an error is encountered) and commented out the very last parameter.  Once we apply SP2 on the Subscriber, I will go back in and uncomment the last parameter to gain the additional error logging detail.

2 comments:

  1. Nice! Glad to hear you recovered from that one. We had something similar, this time with 2 PUB's, upgraded the 1 PUB with bi-directional\2-Way\UpdatableSubscriber whilst the other PUB (the SUB to the PUB we upgraded) was left on older patch level, and the upgrade of replication removed the system stored-procedure but never recreated it:System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.sp_MSsync_ins_TableName_Nr'.
    Meaning updates at the subscriber never made its way back to the publisher...
    A simple drop and recreation of the subscription solved it.

    ReplyDelete