SQL Server 2005 Replication Error : The Distributor has not been installed correctly
Last updated: November 1st '05 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New

Click here to find out the top 15 SQL Server books purchased by this site's visitors! NEW

Featured Book:
Real-World SQL-DMO for SQL Server, by Allan Mitchell and Mark Allison

Book of the month:
Click here to find out more about this excellent book!
NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line

SQL Server 2005 Replication Error : The Distributor has not been installed correctly

This is a short article to make you aware of an error returned by "Expired subscription clean up" job, when you setup replication in SQL Server 2005 (Yukon). Though the "Expired subscription clean up" job fails with the below error, there is nothing wrong with your replication setup. It is a bug in one of the replication system stored procedures. I wanted to highlight this, just to spare you from reinstalling replication, thinking you set it up incorrectly.

The Error:
The Distributor has not been installed correctly. [SQLSTATE 42000] (Error 20036)

Once replication is setup, the "Expired subscription clean up" job on the distributor SQL Server fails with the above error. But replication functions normally, without any problems.

The Scenario:
You may ignore the error, only in a scenario, where the distributor itself is not marked as a publisher, but has remote publishers. So, you are likely to see this error, when you have a dedicated SQL Server 2005 distribution server, that is not publishing any of its own databases.

The reason:
Using SQL Server Profiler, I have tracked it down to the following query in sys.sp_MSrepl_getdistributorinfo

        SELECT @loc_distribdb = msd.distribution_db,
                @loc_publishertype = msd.publisher_type,
                @loc_publisherid = ss.server_id,
                @loc_working_directory = msd.working_directory,
                @loc_version = @@microsoftversion
        FROM msdb.dbo.MSdistpublishers as msd join sys.servers as ss 
            ON msd.name = UPPER(ss.name)
                AND msd.name = @publisher

This query does not return any rows, if the distributor itself is not marked as a publisher. In this scenario sys.sp_expired_subscription_cleanup raises the above error.

The Workaround:
To get around the problem, you will have to mark the distribution server as a publisher as well. But you don't have to publish any articles from the distribution server's databases.

To mark the distributor as a publisher, follow these steps in SQL Server Management Studio (SSMS):

   - Connect to the replication distribution server
   - Right click on the "Replication" node in the server tree
   - Select "Distributor Properties..." from the popup menu
   - Click on the "Publishers" page in the left hand side pane
   - Tick the check box against the name of the distribution server
   - Click "OK", and provide any other requested information

The same can be performed from the query window of SSMS, using a system stored procedure called, sp_adddistpublisher. For more information about the parameters required by this procedure, see SQL Server 2005 Books Online.

I have reported this to Microsoft, and a bug has been filed. I am not sure when this is going to be fixed though. If many DBAs complain, then it'll probably get fixed in SQL Server 2005 Service Pack 1 (SP1).

Disclaimer, terms of use and privacy policy
Copyright 1997 - 2006 Narayana Vyas Kondreddi. All rights reserved.