Msg 3724...Cannot drop the procedure...because...used for replication

Question

Post reply

Msg 3724...Cannot drop the procedure...because...used for replication

Ronzo

Hall of Fame

Points: 3917
More actions
April 29, 2010 at 10:18 am

#220102

Thought I understood replication fairly well. Client has SP I need to drop, but get error about it being used for replication. Except it isn't (as far as I can tell). It is not an article in any existing publication (checked with SSMS and distriubtion DB tables), and the procedure object is not flagged as being published (checked sysobjects (category) and sys.objects (is_published & is_schema_published), as well as sys.procedures (is_published).
I'm at a loss. Where else can I look to determine why I can't drop this procedure? And just as importantly, how can I drop it?
SQL Server 2005 SP2 32bit
Have Fun!
Ronzo

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Did you look in dbo.sysarticles ?

1. Once you figure out which subscriber has it (if any) you do this:

EXEC dbo.sp_dropsubscription @publication = N' publication name',

@article = N' your proc name',

@subscriber = ' whatever server name if you found one (repeat for as many as you find)',

@destination_db = N' db name at subscriber'

2. Once you are done with the above (again if you found subscribers) you must run:

EXEC dbo.sp_droparticle @publication = N' publication name', @article = N' your proc name'

3. Go home free 😀

* Noel

Ronzo Hall of Fame Points: 3917 More actions · Answer 2

Nope, not in sysarticles. If you try to drop it anyway, you get error "The article...does not exist".

Any other ideas?

Have Fun!
Ronzo

noeld SSC Guru Points: 96590 More actions · Answer 3

Well I assumed you had "transactional replication" maybe you have "merge"; can you check sysmergearticles ?

* Noel

Ronzo Hall of Fame Points: 3917 More actions · Answer 4

Good assumption (I think). There is no sysmergearticles in the database in question.

Customer had "piggybacked" some replication of an unknown nature on our delivered system that uses transactional replication. We replicate tables and 3 specific stored procedures. The customer apparently setup replication of "everything under the sun", then "removed" it.

We are left with the aftermath, and for the most part all seems fine...except this problem with stored procedures.

Have Fun!
Ronzo

noeld SSC Guru Points: 96590 More actions · Answer 5

My only advice in that case would be to remove replication the "hard way" and start from scratch.

EXEC sp_removedbreplication 'dbname'

* Noel