Replication Error:14046

• litu deb

  SSC Eights!

  Points: 818

  More actions

  July 16, 2008 at 2:44 am

  #189535

  In transactional replication,I am getting the following error..

  Could not drop article. A subscription exists on it. [SQLSTATE 42000] (Error 14046) exec sp_AddArticle failed in sproc_AddArticleToPublication [SQLSTATE 01000] (Error 0).

  my question is :how to identify which article has the problem .

  OR

  Please give me some alternate solution..

  Thanks,

  Litu

• noeld

  SSC Guru

  Points: 96590

  More actions

  July 17, 2008 at 2:05 pm

  #844647

  litu deb (7/16/2008)

  ---

  In transactional replication,I am getting the following error..

  Could not drop article. A subscription exists on it. [SQLSTATE 42000] (Error 14046) exec sp_AddArticle failed in sproc_AddArticleToPublication [SQLSTATE 01000] (Error 0).

  my question is :how to identify which article has the problem .

  OR

  Please give me some alternate solution..

  Thanks,

  Litu

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

  check the result set for your answer!

  
  * Noel

• litu deb

  SSC Eights!

  Points: 818

  More actions

  August 11, 2008 at 8:41 am

  #856118

  Thanks for the query.

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

  this query show about all the article,From here how can I identify which article has problem.

  Please help..

  Thanks,

  Litu Deb

• noeld

  SSC Guru

  Points: 96590

  More actions

  August 11, 2008 at 9:27 am

  #856159

  litu deb (8/11/2008)

  ---

  Thanks for the query.

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

  this query show about all the article,From here how can I identify which article has problem.

  Please help..

  Thanks,

  Litu Deb

  I thought you knew the "ID" of the article. and that's why I recommended sp_helparticle so that you could locate the offending item.

  If you don't know the ID (article_id) you should look in the distribution database for the subscriptions that are enabled on MSsubscriptions table ( select * from distribution.dbo.MSsubscriptions )

  HTH

  
  * Noel

• litu deb

  SSC Eights!

  Points: 818

  More actions

  August 12, 2008 at 4:32 am

  #856569

  When I am running the following command ,getting more than 600 rows,how I will check which article has problem..

  select * from distribution.dbo.MSsubscriptions where subscriber_id=0

  Thanks,

  Litu Deb

• hugowrd

  Valued Member

  Points: 73

  More actions

  May 19, 2010 at 7:37 am

  #1169425

  Hi !

  I've a similar problem, i have a replication between SS2k5 and Oracle db, and every day i drop and add new articles because they are tables with dally data. When i try to drop a subscription and the following article via SQL Job with the following T-SQL:

  ...

  EXEC sp_dropsubscription @publication = @EXECStrPublicationName,

  @article = @EXECStrTableName,

  @publisher = @EXECStrPublisherName,

  @subscriber = @EXECStrSubscriberName,

  @destination_db = @EXECStrDestination_DbName

  GO

  ...

  it returns the following error:

  Executed as user: SQLAdmin. Could not drop article. A subscription exists on it. [SQLSTATE 42000] (Error 14046) Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot. [SQLSTATE 01000] (Error 20605). The step failed.

  I've tried the post's above, without any luck.

  Any ideia ?

• sejal p gudhka

  SSCrazy

  Points: 2102

  More actions

  May 25, 2010 at 12:43 am

  #1171867

  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