I ran below script to drop the existing subscription and tried recreat the subscription.
@publication = 'xxxxx',
@article = N'all',
@subscriber = 'xxxxx'
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'
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?
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.