Not able to setup distribution on publisher database server in sql server 2008 r2

Question

Hi,

I am able to launch replication monitor on this server, when I need to add publication then popup window comes with message that you need to run this script on distribution database, but I don't see that distribution database is present on the server.

When I try to get this done via script then it gives below error:

Msg 21482, Level 16, State 1, Procedure sp_MSreplagentjobexists, Line 95

sp_MSreplagentjobexists can only be executed in the "distribution" database.

It seems distribution  is not setup correctly.

Not able to setup distribution on publisher database server in sql server 2008 r2.

Any clue what I am missing on here.

---

Santosh Singh

Answer 1

It is possible the distribution database was accidentally dropped without properly disabling publishing and distribution, or perhaps something else.

Please execute sp_helpdistributor at the Publisher on any database to list possible information about the Distributor and distribution database.

---

Brandon Williams (blog | linkedin)

Answer 2

Yes I agree with you.

It got removed accidently from somebody.

I had clean all and then it allowed to me create new distributor setup.

After that all worked fine: Very useful some queries to cross check what all are setup on the servers:

USE master;

go

--Is the current server a Distributor?

--Is the distribution database installed?

--Are there other Publishers using this Distributor?

EXEC sp_get_distributor

--Is the current server a Distributor?

SELECT is_distributor FROM sys.servers WHERE name='repl_distributor' AND data_source=@@servername;

--Which databases on the Distributor are distribution databases?

SELECT name FROM sys.databases WHERE is_distributor = 1

--What are the Distributor and distribution database properties?

EXEC sp_helpdistributor;

EXEC sp_helpdistributiondb;

EXEC sp_helpdistpublisher;

Also sys.database is_published column didn't allow me to set that off for that existing db.

It was also case of somebody mistakenly set user db as distributor and that caused a lot of I guess.

Finally I had to check

sp_changedistributiondb procs and got to know that

msdb..MSdistributiondbs

has all the distributor database information was kept. So I removed from that old db information from there and all other dependent views which were  disturbing me to change the db for distributor role, got stopped erroring me now and then.

After that I created new distributor db using wizard and then all worked for me.

Thanks for all help with this.

---

Santosh Singh