Monthly Archives: January 2015

[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

1 Comment

Filed under Uncategorized

[SOLVED]: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’

Dear All,

Today I have restored all Databases of mysql 5.1 to mysql 5.5 and connected with user root  and  faced a strange issue for below commands

mysql> CREATE USER ‘abcd’@’%’ IDENTIFIED BY ‘password’;

mysql> grant all privileges on *.* to ‘root’@’localhost’ identified by ‘password’ with grant option;

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

 

As the problem was strange answer was also strange. I tried lots of options and solution was very simple and I enjoyed it.

Steps taken to resolve the issue

  • Connected the source server of which all databases backup was restored say server A
  • Connected to destination server on which all databases are restored say server B
  • On Server A right click on MYSQL database and select copy database
  • In target window select server B and database as mysql in databases list
  • Click on copy button
  • That’s all

After that stop and start MYSQL service of destination server ( server B) , error will not come J

Thanks

Prashant Deshpande

Leave a comment

Filed under Uncategorized