Recently I blogged about two methods to repair transactional replication.
Today I was attempting to apply the same concept as method #2 (which is from Edward Polley’s – Super Fast Transactional Replication Repair – article) when I came across an oddity in Microsoft’s error reporting.
I was executing sp_addmergesubscription, which is not the same stored procedure that Edward was referring to in his article, but I passed in ‘replication support only’ for the @sync_type parameter anyways. I then received this error message:
Msg 14052, Level 16, State 1, Line 1
The @sync_type parameter value must be “automatic”, “none”, “replication support only”, “initialize with backup”, or “initialize from lsn”.
Obviously, I first checked my spelling. I had spelled everything correctly so I was then off to figure out why I was getting an error which explicitly told me my parameter was correct.
So next I looked into the T-SQL of sys.sp_addmergesubscription. Here is where I found what I’m calling; the sloppiness.
[sql]/*
** Parameter Check: @sync_type.
** Set sync_typeid based on the @sync_type specified.
**
** sync_typeid sync_type
** =========== =========
** 1 automatic
** 2 none
*/
IF LOWER(@sync_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN (‘automatic’, ‘none’)
BEGIN
RAISERROR (14052, 16, -1)
RETURN (1)
END[/sql]
If you were to run the RAISERROR line in the above script you will receive the message indicating 5 potential options for the @sync_type parameter but the IF statement clearly only accepts 2.
I’ve found this to be because Microsoft re-used the error message which was intended for sys.sp_MSrepl_addsubscription (called by sys.sp_addsubscription) on the merge replication side of the feature rather than creating a new, more clear, error message.
Leave a Reply