Recently I blogged about two methods to repair transactional replication.<\/p>\n
Today I was attempting to apply the same concept as method #2 (which is from Edward Polley\u2019s –\u00a0Super Fast Transactional Replication Repair<\/a>\u00a0– article) when I came across an oddity in Microsoft\u2019s error reporting.<\/p>\n I was executing sp_addmergesubscription<\/a>, which is not the same stored procedure that Edward was referring to in his article, but I passed in \u2018replication support only\u2019 for the\u00a0@sync_type parameter anyways. I then received this error message:<\/p>\n > Msg 14052, Level 16, State 1, Line 1 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.<\/p>\n So next I looked into the T-SQL of\u00a0sys.sp_addmergesubscription<\/a>. Here is where I found what I\u2019m calling;\u00a0the sloppiness<\/em>.<\/p>\n Parameter Check: @sync_type.<\/p>\n <\/strong> Set sync_typeid based on the @sync_type specified.<\/p>\n <\/p>\n <\/strong> sync_typeid sync_type<\/p>\n =========== =========<\/p>\n <\/strong> 1 automatic<\/p>\n <\/em> 2 none<\/p>\n <\/em>\/<\/p>\n IF LOWER(@sync_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('automatic', 'none')<\/p>\n BEGIN<\/p>\n RAISERROR (14052, 16, -1)<\/p>\n RETURN (1)<\/p>\n END<\/code><\/p>\n 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.<\/p>\n I\u2019ve found this to be because Microsoft re-used the error message which was intended for\u00a0sys.sp_MSrepl_addsubscription<\/a> (called by\u00a0sys.sp_addsubscription<\/a>) on the merge replication side of the feature rather than creating a new, more clear, error message.<\/p>\n","protected":false},"excerpt":{"rendered":" Recently I blogged about two methods to repair transactional replication. Transactional Replication \u2013 Fixed in Minutes Today I was attempting to apply the same concept as method #2 (which is from Edward Polley\u2019s –\u00a0Super Fast Transactional Replication Repair\u00a0– article) when I came across an oddity in Microsoft\u2019s error reporting. I was executing sp_addmergesubscription, which is […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-29","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/29","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=29"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/29\/revisions"}],"predecessor-version":[{"id":292,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/29\/revisions\/292"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=29"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=29"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=29"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n>
\n> The @sync_type parameter value must be \u201cautomatic\u201d, \u201cnone\u201d, \u201creplication support only\u201d, \u201cinitialize with backup\u201d, or \u201cinitialize from lsn\u201d.<\/p>\n\/<\/p>\n