Advertisements
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.
Status:
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).
