We have a server setup as a distribution server, which manages the replication to & from 3 other SQL servers.
Last night something weird happened... Replication to & from the servers are fine except for one server. You cannot edit ANY setting, remove ANY publication or subscriber, or add a publication. the following error pops up...
The Distributor is not available. Error 208: Invalid object name 'msdb.dbo.MSdistpublishers'
What's really freaky is that replication is still running 100%
Also, when you run SELECT @@SERVERNAME on the problem server, it returns NULL.
Does anybody have a clue...???
Thanks in advance!!!
Did you have to recreate master with rebuildm.exe and then restore master from backup, at any point along the way?
We just had this exact problem as a result of a corrupted volume that required us to recreate and restore master. @@servername was null. select * from master..sysservers did not show the local server as a row with srvid = 0 - it was another srvid row thus @@servername was not finding it. This was on SQL 2000 SP3a and is a cluster instance.
We ran sp_dropserver on the local instance name (sp_dropserver 'server\server'). The extra row in sysservers was removed. We ran sp_addserver 'server\server', 'local'. This added the srvid = 0 row back to master..sysservers. (Aside - I have no idea what would happen if you did not sp_dropserver and instead did sp_addserver 'server\server', 'local', 'duplicate_OK').
You have to restart the SQL instance to get @@servername back.
Strange replication errors went away. One of the errors you get with this is
"Error 14114: (NULL) is not configured as a distributor" http://support.microsoft.com/kb/302223/en-us gives the @@servername clue. I suspect EM uses replication stored procedures, and these stored procedures are relying on @@servername having a value. sp_helpdistributor for example uses @@servername all over the place. We had the subject error too. I bet it's looking for MSdistpublishers on a server named 'null'.
I had this same thing happen on another server where we had to rebuild master, about a year ago. @@servername = null on that one too. I suspect there is some glitch in the master rebuild / restore / restart process.
Hope this post helps somebody else, because I've scratched my head on this twice now. It is difficult to find this solution.
Also, if your server got renamed you are in for that same problem!!!