[SOLVED] invalid column name ‘publisher_type’ error 207 in MSSQL Replication

Dear All,

This post refers to  MSSQL replication error “ invalid column name ‘publisher_type’ error 207 ”

Today I have shifted MSSQL Database server from one VM to new VM. The process which I followed was

  • Stopped SQL server service from SERVER A
  • Copied all USER databases to SERVER B
  • Attached all databases on SEREVR B

All went successful J .

Now while configuring REPLICATION I got error  on server B

Error 208: Invaild object name ‘msdb.dbo.MSdistpublishers’

I understood the error was coming because databases were already published on SERVER A and not distributor was not found So I searched on internet and come to know I need to create two tables

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

 

I done as per internet blogs suggestion which resolved the 208 ERROR but started a new error invalid column name ‘publisher_type’ error 207  , on executing sp_dropdistpublisher for which didin’t got success by searching on net so followed below steps

  • Opened sp_dropdistpublisher stored procedure in master database and tried to add comment for ‘publisher_type’ , but SQL was not allowing to change system procedures
  • As sql server was not allowing to work with system procedures I have added column as

publisher_type  Varchar (50)   in table MSdistpublishers table which was recently created by me.

  • Added value for the same column as MSSQLSERVER
  • And then tried to execute sp_dropdistpublisher and got success

Then newly configured distributor and implemented replication J

Thanks

Prashant Deshpande

Advertisements

1 Comment

Filed under Uncategorized

One response to “[SOLVED] invalid column name ‘publisher_type’ error 207 in MSSQL Replication

  1. Mithril

    thanks a lot !
    had a corrupted replication environment after mishap with MSDB… you saved our prod..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s