October 18, 2005 at 10:26 am
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!!!
October 21, 2005 at 8:00 am
This was removed by the editor as SPAM
July 14, 2006 at 11:39 am
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.
July 18, 2006 at 10:59 am
Also, if your server got renamed you are in for that same problem!!!
Cheers,
* Noel
October 29, 2008 at 3:08 pm
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
January 21, 2009 at 6:42 am
Any ideas why the object would disappear in the first place?
January 21, 2009 at 1:38 pm
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.
December 17, 2009 at 3:58 pm
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
January 25, 2010 at 12:29 pm
@ 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.
January 26, 2010 at 2:44 pm
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!
June 1, 2012 at 1:30 pm
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!!
June 1, 2012 at 1:49 pm
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)
December 22, 2016 at 6:58 am
Any ideas why the object would disappear in the first place?
July 10, 2017 at 6:13 am
This was removed by the editor as SPAM
July 10, 2017 at 6:16 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply