Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am running into errors:

exec sp_dropdistpublisher @publisher='servername'


Msg 21047, Level 16, State 1, Procedure sp_dropdistpublisher, Line 145
Cannot drop the local distribution Publisher because there are Subscribers defined.

There is no subscriber defined, and I don't know where to find the information that makes SQL Server think that there is a subscriber defined.

I'm open to any suggestions...

share|improve this question
If I add @ignore_distributor=1, the distribution is dropped. But I am hoping to understand what the problem is--not just use the workaround – Nathan Garabedian Feb 2 '11 at 21:15
So this became a nonissue after discovering that it was the database installation was corrupted? – jcolebrand Feb 17 '11 at 0:23
up vote 2 down vote accepted

Have you tried using this to enumerate the list of subscribers to find if it thinks there are some defined? Notice that I'm just starting in sp_dropdistpublisher and reading the code to see where it checks before deleting. Just an idea.

-- Name:
-- sp_MSrepl_enumsubscriptions
-- Description:
--  Enumerate subscriptions on a database
-- Returns:
--  0 == Failed
--  1 == Succeed
-- Security:
--  public, db_owner check
--  Requires Certificate signature for catalog access
-- Notes:
--  Used by the UI to generate a list of subscriptions
share|improve this answer
I tried that, and got an empty resultset. I will try reading the code there to see what happens, but in the meantime, I set up another set of servers and tried the same replication steps and everything seems to be working without errors. That makes me think there's some corruption going on within the SQL software that may require a reinstall. – Nathan Garabedian Feb 4 '11 at 17:58
Oh well :\ ... that's usually the best way to define it :\ – jcolebrand Feb 4 '11 at 19:06
I'm going to accept your answer, because it is helpful. – Nathan Garabedian Feb 21 '11 at 16:00

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.