SQLServerCentral / SQL Server 7,2000 / Replication / Error 208: Invalid object name ''msdb.dbo.MSdistpublishers'' / Latest Posts

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

bguo — Fri, 01 Jun 2012 13:49:36 GMT

Our OS hardware had problem, we reinstalled SQL, then drop the distributor database, ... had problem, got error: Invalid object name 'msdb.dbo.MSdistributiondbs'. (Microsoft SQL Server, Error: 208) here is what I did, worked 1. restore the old 'msdb' to database as old_msdb' 2. script table [dbo].[MSdistributiondbs] from old_msdb' to run under the current msdb: use msdb CREATE TABLE [dbo].[MSdistributiondbs]( [name] [sysname] NOT NULL, [min_distretention] [int] NOT NULL, [max_distretention] [int] NOT NULL, [history_retention] [int] NOT NULL ) ON [PRIMARY] GO 3. when I try to use configure distributor, got another error: Invalid object name 'msdb.dbo.MSdistpublishers', then I did script table MSdistpublishers to current msdb use msdb CREATE TABLE [dbo].[MSdistpublishers]( [name] [sysname] NOT NULL, [distribution_db] [sysname] NOT NULL, [working_directory] [nvarchar](255) NOT NULL, [security_mode] [int] NOT NULL, [login] [sysname] NOT NULL, [password] [nvarchar](524) NULL, [active] [bit] NOT NULL, [trusted] [bit] NOT NULL, [thirdparty_flag] [bit] NOT NULL, [publisher_type] [sysname] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[MSdistpublishers] ADD DEFAULT (N'MSSQLSERVER') FOR [publisher_type] GO Then, everything is fine. check 2 tables 1. MSdistributiondbs 2. MSdistpublishers are under msdb/table, not under msdb/table/systable. It is fine. Worked!! Baimei Guo (http://www.cdbaby.com/cd/baimei)

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

bguo — Fri, 01 Jun 2012 13:30:22 GMT

Our OS has problem, we reinstalled SQL, then drop the distributor database, ... had same problem error: Invalid object name 'msdb.dbo.MSdistributiondbs'. (Microsoft SQL Server, Error: 208)here is what I did, worked1. restore the old 'msdb' to database as old_msdb'2. script table [dbo].[MSdistributiondbs] from old_msdb' to run under the current msdb:use msdbCREATE TABLE [dbo].[MSdistributiondbs]( [name] [sysname] NOT NULL, [min_distretention] [int] NOT NULL, [max_distretention] [int] NOT NULL, [history_retention] [int] NOT NULL) ON [PRIMARY]GO3. when I try to use configure distributor, got other error: Invalid object name 'msdb.dbo.MSdistpublishers', then I did script table MSdistpublishers to current msdb use msdbCREATE TABLE [dbo].[MSdistpublishers]( [name] [sysname] NOT NULL, [distribution_db] [sysname] NOT NULL, [working_directory] [nvarchar](255) NOT NULL, [security_mode] [int] NOT NULL, [login] [sysname] NOT NULL, [password] [nvarchar](524) NULL, [active] [bit] NOT NULL, [trusted] [bit] NOT NULL, [thirdparty_flag] [bit] NOT NULL, [publisher_type] [sysname] NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[MSdistpublishers] ADD DEFAULT (N'MSSQLSERVER') FOR [publisher_type]GOThen, everything is fine. check 2 tables1. MSdistributiondbs2. MSdistpublishersare under msdb/table, not under msdb/table/systable. It is fine. Worked!!

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

hansel.ortiz — Tue, 26 Jan 2010 14:44:27 GMT

hi guys.. sorry if my english is not so good..im just reading this post because im having the same issue..i getting an error 208 trying to create a publication, after several tryes of creating the publication (Transactional replication) i got the same error.. but with a little diference 'Invalid object name dbo.syspublications' --> I don't know in what database is that object.. Steps done in several triesMSDB DATABASE --> Recreation (Cheked)@@servername --> not null (Cheked)Distribution database --> RECREATION (Checked)Linked server --> Server is connected to a linkin server (what is going to be the subscriber server)--(CHECKED)i just want to know if it is better droping all the information of replication in the server.. and just start over, because getting the same error 'Invalid object name dbo.syspublications'Server information: Sql Server 2005 SP3 Transactional replication (Creating Publication)thanks and waiting for some information to solve this!

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

Kumaran Govender — Mon, 25 Jan 2010 12:29:27 GMT

@ 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.

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

starsthescqw — Mon, 25 Jan 2010 11:55:41 GMT

[quote][b]Kumaran Govender (10/29/2008)[/b][hr]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 [b]NOT[/b] 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 :DUSE msdbGOCREATE TABLE [MSdistributiondbs] ([name] [sysname] NOT NULL ,[min_distretention] [int] NOT NULL ,[max_distretention] [int] NOT NULL ,[history_retention] [int] NOT NULL) ON [PRIMARY]GOCREATE 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[/quote]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 [url=http://racebusters.co.uk/]Horse Racing Tips[/url] on this page.

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

Ed7 — Thu, 17 Dec 2009 15:58:29 GMT

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 isInvalid object name ‘syspublications’Invalid object ‘syssextendedarticlesview’ (Microsoft SQL Server, Error 208).Then, I clicked OK. The second error isInvalid 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

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

Kumaran Govender — Wed, 21 Jan 2009 13:38:13 GMT

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.

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

Nick-957131 — Wed, 21 Jan 2009 06:42:09 GMT

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

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

Kumaran Govender — Wed, 29 Oct 2008 15:08:59 GMT

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 [b]NOT[/b] 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 :DUSE msdbGOCREATE TABLE [MSdistributiondbs] ([name] [sysname] NOT NULL ,[min_distretention] [int] NOT NULL ,[max_distretention] [int] NOT NULL ,[history_retention] [int] NOT NULL) ON [PRIMARY]GOCREATE 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

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

noeld — Tue, 18 Jul 2006 10:59:00 GMT

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

Cheers,

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

Joe Nospam — Fri, 14 Jul 2006 11:39:00 GMT

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.

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

Site Owners — Fri, 21 Oct 2005 08:00:00 GMT

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!

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

D1rtyD0g — Tue, 18 Oct 2005 10:26:00 GMT

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