SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error 208: Invalid object name ''msdb.dbo.MSdistpublishers''


Error 208: Invalid object name ''msdb.dbo.MSdistpublishers''

Author
Message
D1rtyD0g
D1rtyD0g
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 439

Hi Guys,

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!!!


Site Owners
Site Owners
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Administrators
Points: 12689 Visits: 21
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
Joe Nospam
Joe Nospam
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 1

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.


noeld
noeld
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22930 Visits: 2048

Also, if your server got renamed you are in for that same problem!!!

Cheers,




* Noel
Kumaran Govender
Kumaran Govender
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 17
Hi Guys,

I know that is is an old topic, but the net does not seem to come up with an easy solution. After playing around for a bit I managed to fix it, here is the soltion:-

After ensuring that "SELECT @@SERVERNAME" is NOT NULL ensure that the '[MSdistributiondbs]' and '[MSdistpublishers] ' tables are NOT present in the msdb database. If the are remove then and use the code below to add them back. Once this is done the "Error 208:..." will disappear and you can proceed in setting up your replication.
Good Luck BigGrin

USE msdb
GO

CREATE TABLE [MSdistributiondbs] (
[name] [sysname] NOT NULL ,
[min_distretention] [int] NOT NULL ,
[max_distretention] [int] NOT NULL ,
[history_retention] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MSdistpublishers] (
[name] [sysname] NOT NULL ,
[distribution_db] [sysname] NOT NULL ,
[working_directory] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[security_mode] [int] NOT NULL ,
[login] [sysname] NOT NULL ,
[password] [nvarchar] (524) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[active] [bit] NOT NULL ,
[trusted] [bit] NOT NULL ,
[thirdparty_flag] [bit] NOT NULL
) ON [PRIMARY]
GO
Nick-957131
Nick-957131
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 224
Any ideas why the object would disappear in the first place?
Kumaran Govender
Kumaran Govender
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 17
I only noted the issue when we were trying to reinstated replication. The server use to perfrom transactional replication some time back but it was removed. Now when we were trying to re-add it the objects were missing. I cannot say for a 100% fact, but this could have been the issue in my case.
Ed7
Ed7
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1749 Visits: 930
Hello Kumaran,

I almost have same replication issues when I tried to disable Publishing and Distribution through Wizard in SQL Server 2000.

My replication environment are:
-- Publisher (MS SQL Server 2000 SP3)
-- A remote distributor (MS SQL Server 2008 SP1)
-- A Subscriber (MS SQL Server 2008 SP1)

The first error is
Invalid object name ‘syspublications’
Invalid object ‘syssextendedarticlesview’ (Microsoft SQL Server, Error 208).

Then, I clicked OK.
The second error is
Invalid object name ‘syssubscriptions’ Change database context to ‘master’. (Microsoft SQL Server, Error 208).

I noticed that the MSDB is missing both table [MSdistributiondbs] and [MSdistpublishers]. I applied your suggestion on this forums by adding those missing table in MSDB.

Then, I started to disble Publishing and Distribution via Wizard and got the same errors.

I have spend a lot of time and searched the solutions on internet posting. End up with no luck.

Can anyone help and provide me a solution.

Thanks in advance.
TJ
starsthescqw
starsthescqw
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 9
Kumaran Govender (10/29/2008)
Hi Guys,

I know that is is an old topic, but the net does not seem to come up with an easy solution. After playing around for a bit I managed to fix it, here is the soltion:-

After ensuring that "SELECT @@SERVERNAME" is NOT NULL ensure that the '[MSdistributiondbs]' and '[MSdistpublishers] ' tables are NOT present in the msdb database. If the are remove then and use the code below to add them back. Once this is done the "Error 208:..." will disappear and you can proceed in setting up your replication.
Good Luck BigGrin

USE msdb
GO

CREATE TABLE [MSdistributiondbs] (
[name] [sysname] NOT NULL ,
[min_distretention] [int] NOT NULL ,
[max_distretention] [int] NOT NULL ,
[history_retention] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MSdistpublishers] (
[name] [sysname] NOT NULL ,
[distribution_db] [sysname] NOT NULL ,
[working_directory] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[security_mode] [int] NOT NULL ,
[login] [sysname] NOT NULL ,
[password] [nvarchar] (524) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[active] [bit] NOT NULL ,
[trusted] [bit] NOT NULL ,
[thirdparty_flag] [bit] NOT NULL
) ON [PRIMARY]
GO


Thanks alot for this Kumaran, this solved the problem for me. It's strange that this problem has been happening to people for ages yet it's still happening. Some things just never change obv. Anyway thanks for your help.

Phen375 is a fat burner that seems popular right now. I saw on http://skinnyexpress.com/phen375-review that it is an energy booster too.
Kumaran Govender
Kumaran Govender
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 17
@ starsthescqw: You are welcome

@ Edwin-376531:
- Have you ran the scripts listed above on your server? (ignore this, I just reread your post)
- I cannot identify the object 'syssextendedarticlesview’ - I will research this more and get back to you. Once the definition of this object can be found, you can apply it to the MSDB database then retry the replication config.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search