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:
@publication = 'ARSYSTEM_PUBLISHER',
@article = 'HPD_Help_Desk',
@force_invalidate_snapshot = 1;
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.
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>
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.
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);
@publication = @publication,
@article = N'all',
@subscriber = @subscriber;
Here is the workaround:
Step1: Find out the 'article_id' for which it is failing for.
select article_id from msarticles with (nolock) where article = '<article>'
Step2: Find out the subscriber_id's of that article.
select * from mssubscriptions with (nolock) where article_id = <xxx>
Step3: Delete them from the mssubscriptions table
delete from MSsubscriptions with (rowlock) where article_id = <xxx>
We should now be good to drop that article.
- Proposed as answer by Vijay VJ Sunday, June 03, 2012 1:45 AM
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 = '%'