1

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?

4

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 

enter image description here

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.