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

  • D1rtyD0g

    Ten Centuries

    Points: 1045

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

  • This was removed by the editor as SPAM

  • Joe Nospam

    Valued Member

    Points: 65

    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

    SSC Guru

    Points: 96590

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

    Cheers,

     


    * Noel

  • Kumaran Govender

    Ten Centuries

    Points: 1029

    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 😀

    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

    Default port

    Points: 1498

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

  • Kumaran Govender

    Ten Centuries

    Points: 1029

    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

    SSCrazy Eights

    Points: 8509

    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

  • Kumaran Govender

    Ten Centuries

    Points: 1029

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

  • hansel.ortiz

    Mr or Mrs. 500

    Points: 585

    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 tries

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

  • bguo

    SSC Veteran

    Points: 290

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

  • bguo

    SSC Veteran

    Points: 290

    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)

  • geminiunderhill

    SSC Journeyman

    Points: 75

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

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply