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

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 7,2000 » Replication » Error 208: Invalid object name...

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

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

Expand / Collapse

Author

Message

Posted Tuesday, October 18, 2005 10:26 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 12:27 AM
Points: 69, Visits: 271

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

Post #229812

Posted Friday, October 21, 2005 8:00 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,040, Visits: 1

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!

Post #231159

Posted Friday, July 14, 2006 11:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 02, 2006 4:31 PM
Points: 1, 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.

Post #294558

Posted Tuesday, July 18, 2006 10:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Thursday, June 06, 2013 9:12 AM
Points: 6,260, Visits: 1,980

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

Cheers,

* Noel

Post #295245

Kumaran Govender

Posted Wednesday, October 29, 2008 3:08 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, January 25, 2010 12:24 PM
Points: 79, 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 :D

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

Post #593961

Posted Wednesday, January 21, 2009 6:42 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 10:03 AM
Points: 50, Visits: 202

Any ideas why the object would disappear in the first place?

Post #640656

Kumaran Govender

Posted Wednesday, January 21, 2009 1:38 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, January 25, 2010 12:24 PM
Points: 79, 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.

Post #641124

Posted Thursday, December 17, 2009 3:58 PM

Old Hand

Group: General Forum Members
Last Login: Monday, May 20, 2013 10:00 PM
Points: 347, Visits: 888

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

Post #836041

Posted Monday, January 25, 2010 11:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2013 4:40 AM
Points: 1, Visits: 6

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 :D

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.

There are some free Horse Racing Tips on this page.

Post #853208

Kumaran Govender

Posted Monday, January 25, 2010 12:29 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, January 25, 2010 12:24 PM
Points: 79, 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.

Post #853233

« Prev Topic | Next Topic »

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse