I've got a large number of articles I need to add to a new replication transactional publication in a dynamic fashion, but appear to be running into an error every time I switch replication on.
Specifically, the error I'm getting is that the publication is telling me that the replication stored procedures aren't there, despite me telling it to specifically create them.
I don't believe this is a security thing as the dev server I'm working on is completely open and basically everyone/everything has sysadmin access.
From my investigations, it appears that when I'm running sp_addarticle from a dynamic SQL string within a cursor, even though I've explicitly set the @schema_option to create the replication sprocs, it's failing to do so.
For reference, I'm doing things in the following order:
- Create the publication in an inactive state
- Configuring it accordingly (adding all the necessary options/security settings, including the publication snapshot)
Inside a loop, adding each article to the publication sequentially with the following settings.
@type = N'logbased' @schema_option = 0x000000000803509F @identityrangemanagementoption = N''manual'' @destination_table = N''' + @Table + ''' @destination_owner = N''' + @Schema + ''' @vertical_partition = N''false'' @ins_cmd = N''CALL sp_MSins_' + @Table + ''' @del_cmd = N''CALL sp_MSdel_' + @Table + ''' @upd_cmd = N''SCALL sp_MSupd_' + @Table + ''' @status = 0
Inside a second loop
Change the publication status to active Add the subscriber (sp_addsubscription) Add the push agent (sp_addpushsubscriptionagent)
Inside a third loop, change the article status to 'parameters' (MSDN says this is the default, so I'm assuming this is correct)
SELECT @sql = 'exec sp_changearticle @publication = N''' + @Publication + ''', @article = N''' + @Schema + '-' + @Table +''', @property = ''status'', @value = ''parameters''' EXEC (@SQL)
I've had a look through all the options, but I'm honestly starting to get a bit codeblind, so can anyone help me ID why this isn't working?