none
Error on creating a subscription

    Question

  • I ran below script to drop the existing subscription and tried recreat the subscription. 

       USE [xxxx]

    EXEC sp_dropsubscription

      @publication = 'xxxxx',

      @article = N'all',

      @subscriber = 'xxxxx'

    GO

    use [xxxx]

    exec sp_addsubscription @publication = xxxxx, @subscriber =’xxxxxx’, @destination_db = N'xxxxxxx',

    @subscription_type = N'Push', @sync_type = N'replication support only',@article = N'all', @update_mode = N'read only', @subscriber_type = 0

     

    exec sp_addpushsubscription_agent @publication = N'xxxxxxxx',@subscriber = N'xxxxxx', @subscriber_db = N'xxxxxxxx',

    @job_login = null, @job_password = null, @subscriber_security_mode = 1,@frequency_type = 64, @frequency_interval = 1,

    @frequency_relative_interval = 1, @frequency_recurrence_factor = 0,@frequency_subday = 4, @frequency_subday_interval = 5,

      @active_start_time_of_day = 0, @active_end_time_of_day = 235959,@active_start_date = 0, @active_end_date = 99991231,

       @enabled_for_syncmgr = N'False', @dts_package_location =N'Distributor'

       Go

    But I got some error as below

    Msg 20027, Level 11, State 1, Procedure sp_MSadd_subscription, Line 223

    The article '(null)' does not exist.

    Msg 14070, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1208

    Could not update the distribution database subscription table. The subscription status could not be changed.

    Msg 14057, Level 16, State 1, Procedure sp_MSrepl_addsubscription_article, Line 383

    The subscription could not be created.

    Msg 20021, Level 16, State 1, Procedure sp_MSrepl_addpushsubscription_agent, Line 258

    The subscription could not be found.

    This means that article does not exist at subscriber database? should I recreate the publication from scratch ?

    Can some one please help me?

    Saturday, October 12, 2013 6:39 AM

All replies

  • Saturday, October 12, 2013 7:17 AM
  • I see drop subscription but do not see script for drop article. Did you run sp_droparticle statement for that publication?

    It appears to me that you did not clean it correctly so the result of this failure. You may want to run drop subscription and articles one more time and then attempt adding article and subscription. You may want to ignore error message during drop because it may return if something is already dropped.

    Hence, your step should be:

    1. drop subscription

    2. drop article(s)

    3. add article(s)

    4. add subscription


    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.

    Tuesday, October 15, 2013 12:12 AM