Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. Create the publication in an inactive state
  2. Configuring it accordingly (adding all the necessary options/security settings, including the publication snapshot)
  3. 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
    
  4. Inside a second loop

    Change the publication status to active
    Add the subscriber (sp_addsubscription)
    Add the push agent (sp_addpushsubscriptionagent)
    
  5. 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?

Thanks.

share|improve this question
    
can you get this to work with a single article and a single subscriber without all the looping? Also can you show us the actual publication options you are using? –  Sebastian Meine Sep 10 '13 at 22:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.