The Distributor has not been installed correctly. Could not enable database for publishing

Question

while in the process of creating a publication in one of my servers I continuously get the following error message

The Distributor has not been installed correctly. Could not enable database for publishing.

Although I have dropped and re-created the distributor several times.

--==============================================================
-- replication - create publication - complete
-- marcelo miorelli
-- 06-Oct-2015
--==============================================================

select @@servername
select @@version
select @@spid
select @@servicename

--==============================================================
-- step 00 --  configuring the distributor
-- if there is already a distributor AND it is not healthy, 
-- you can have a look at the jobs related to this distributor and
-- MAYBE, if you need to get rid of it, run this step
-- generally you need to run this when adding a publication it says there is a problem with the distributor
--==============================================================

use master
go
sp_dropdistributor 
-- Could not drop the Distributor 'QG-V-SQL-TS\AIFS_DEVELOPMENT'. This Distributor has associated distribution databases.

EXEC sp_dropdistributor 
     @no_checks = 1
    ,@ignore_distributor = 1
GO

--==============================================================
-- step 01 --  configuring the distributor
-- tell this server who is the distributor and the admin password to connect there

-- create the distributor database
--==============================================================

use master
exec sp_adddistributor 
 @distributor = N'the_same_server'
,@heartbeat_interval=10
,@password='#J4g4nn4th4_the_password#'

USE master
EXEC sp_adddistributiondb 
    @database = 'dist1', 
    @security_mode = 1;
GO

--==============================================================
-- check thing out before going ahead and create the publications
--==============================================================

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;  









--==============================================================
-- here you need to have a distributor in place

-- Enabling the replication database
-- the name of the database we want to replicate is COLAFinance
--==============================================================
use master
exec sp_get_distributor


use master
exec sp_replicationdboption @dbname = N'the_database_to_publish', 
                            @optname = N'publish', 
                            @value = N'true'
GO

is there anything missing? any ideas?

Answer 1

I think I have nailed it, what I did is very simple

A call to the procedure sp_adddistpublisher was missing.

Configures a Publisher to use a specified distribution database. This stored procedure is executed at the Distributor on any database. Note that the stored procedures sp_adddistributor (Transact-SQL) and sp_adddistributiondb (Transact-SQL) must have been run prior to using this stored procedure.

I have run added the last command on the script below step01:

--==============================================================
-- step 01 --  configuring the distributor
-- tell this server who is the distributor and the admin password to connect there

-- create the distributor database
--==============================================================

use master
exec sp_adddistributor 
 @distributor = N'the_same_server'
,@heartbeat_interval=10
,@password='#J4g4nn4th4_the_password#'

USE master
EXEC sp_adddistributiondb 
    @database = 'dist1', 
    @security_mode = 1;
GO

exec sp_adddistpublisher @publisher = N'the_same_server', 
                         @distribution_db = N'dist1';
GO

I noticed that now when I call the following proc:

EXEC sp_get_distributor 

I see the distribution db installed column equals 1, I should have noticed this indication before.

If it works I will leave this answer as it is, otherwise I will add to it accordingly.

Answer 2

You must connect to MSSMS with [server\instance] not with [server,port] (this option is not working for exec sp_replicationdboption and you get error: The Distributor has not been installed correctly. Could not enable database for publishing).