none
Error 14046: Could not drop article. A subscription exists on it.

    Question

  • A view is in replication I want to add 4 more fields in it.

    SQL Server 2008 R2 transactional replication.I have remove the view from destination side already.

    I have run on source side:

    USE [Arsystem]
    EXEC sp_droparticle
      @publication = 'ARSYSTEM_PUBLISHER',
      @article = 'HPD_Help_Desk',
      @force_invalidate_snapshot = 1;
    GO

    ----------------------------------------

    Error 14046: Could not drop article. A subscription exists on it.

    While I have already deleted the view from subscriber still it is giving this error.

    Is there any confirm solution.


    Thanks

    Thursday, May 24, 2012 2:18 PM

All replies

  • Dropping the view on the subscriber will not help you to fix this problem.

    You should drop the subscription at the publisher.

    Try using sp_dropsubscription to do the same. The syntax should be:

    exec sp_dropsubscription @publication = ''ARSYSTEM_PUBLISHER', @article = 'HPD_Help_Desk', @subscriber = <subscriber server name>

    For detailed:

    http://msdn.microsoft.com/en-us/library/ms184385(v=sql.105).aspx


    Thanks, Adi

    Thursday, May 24, 2012 2:55 PM
  • Full disclosure:  I haven't actually tried this on a VIEW (and don't have a REPL test bed set up), but I know it works on TABLES.

    Try using T-SQL to add the columns via ALTER VIEW.  For TABLES, the schema changes are picked up by the Log Reader and applied at the subscriber; no dropping articles from the subscription or the requisite reinit.

    Thursday, May 24, 2012 3:01 PM
  • Hi aditya,

    Thanks.I have tried this also: here what is $(Subserver) is confusing.

    The hostname of the server is: NOCDBREMEDY

    The SQL Server is in cluster so should I paste the virtual name?

    DECLARE @publication AS sysname;
    DECLARE @subscriber AS sysname;
    SET @publication = N'AdvWorksProductTran';
    SET @subscriber = $(SubServer);

    USE [AdventureWorks2008R2;]
    EXEC sp_dropsubscription
      @publication = @publication,
      @article = N'all',
      @subscriber = @subscriber;
    GO


    Thanks

    Thursday, May 24, 2012 5:41 PM
  •  I have implemented it in test environment first.

    Command is working fine but in the Publication properties -->Article list the dropped article is showing.

    How to check it is completely removed from replication?


    Thanks

    Friday, May 25, 2012 8:49 AM
  • Here is the workaround:

    Step1: Find out the 'article_id' for which it is failing for.

    use <distribution>
    go
    select article_id from msarticles with (nolock) where article = '<article>'

    Step2:  Find out the subscriber_id's of that article.

    use <distribution>
    go

    select * from mssubscriptions with (nolock) where article_id = <xxx>

    Step3: Delete them from the mssubscriptions table

    use <distribution>
    go

    begin tran
    delete from MSsubscriptions with (rowlock) where article_id = <xxx>
    commit tran

    We should now be good to drop that article.

    • Proposed as answer by Vijay VJ Sunday, June 03, 2012 1:45 AM
    Sunday, June 03, 2012 1:45 AM
  • Hi,

    If I understand your problem correctly, are you trying to delete publication or article, please clarify.

    Meanwhile, below table you can query which will give you all details about articles, publications and subscription in distribution database.

    select * from distribution..mssubscriptions

    2)After that u can find out which article has the problem with the help- of this query

    sp_helparticle  @publication = 'publication'  , @article =  '%'



    Wednesday, June 06, 2012 9:12 AM
  • I was trying to delete the article it is successful.Now I want to add article but whenever I tried to add the article in the same publicationit fails with different errors .

    Thanks

    Wednesday, June 06, 2012 9:25 AM
  • Hi,

    Can i know what type of errors.

    Wednesday, June 06, 2012 9:39 AM