Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Issue with SQL Server 2K5 SP3 CU5

In case you are considering to apply SQL Server SP3 CU5 (http://support.microsoft.com/kb/972511) to your system, I'd like to point out one issue / bug we found with CU5.

Finding: SQL Server 2K5 SP3 CU5 will damage your replication if the following two conditions are met in the replication framework

1. The replication is a transactional replication and it has publications with updatable subscriptions

2. Replication distributor and publisher are  hosted on different sql server instances

The direct error is when you try to do a DML operation on any tables of updatable subscriptions, you will get the following similar error (when I do an insert)

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'pub_db.dbo.sp_MSsync_ins_tblCustomer_8'.

The root cause of this issue is that CU5 messed / renamed the original dbo.sp_MSsync_ins_tblCustomer_8 ( a system stored proc created automatically by the replication setup for updatable subscriptions) on publisher db side, if I checked on my pub_db side, I will find that there is no dbo.sp_MSsync_ins_tblCustomer_8 there, but instead I find a SP with weird name, something like dbo.sp_MSsync_ins_A4F13DFA_8E4D_48AC_BA3A_351ED6CBA5D4

So the obvious thing is that during CU5 patching, the original dbo.sp_MSsync_ins_tblCustomer_8 was renamed (for whatever unknown reason)

We have verified this in three of our different enviornments and in two of the environments, we repeated by uninstalling CU5, re-setting up replication and then re-patching CU5 again, each time, the same issue can be observed

(Note: if the publisher and the distributor share the same instance, there is no such issue)

So please be careful if you want to apply CU5 and your production has the conditions as I outlined above.

The workaround:

After CU5 is applied, drop the publications with updatable subscriptions and then re-setup the publication and subscriptions.


Comments

Posted by Robert Davis on 13 September 2009

Did you report the bug?

http://connect.microsoft.com

Posted by Steve Jones on 14 September 2009

I'm with Robert, be sure you report this. I'll run a note in the newsletter so people know as well.

Posted by aaron.bertrand on 15 September 2009

Agreed, they should be aware of this breakage, and the best way is via Connect.

However the CU5 binary/distro is unlikely to be fixed and re-released.  The "fix" will be to skip CU5 and go straight to CU6 (when it is available).

Posted by Jeffrey Yao on 16 September 2009

Thanks for all the comments. We already informed MS support regarding this issue. We were working with MS support to solve an issue in our production. The issue proves to be a bug in SQL Server, and is solved in SP3 CU4, and since CU5 was released during the support (i.e. case not closed yet), we were told to go with CU5, and that's how we found this issue with CU5.

Leave a Comment

Please register or log in to leave a comment.