"Uninitialized subscription" on replication monitor when creating a subscription on transactional replication

All we need is an easy explanation of the problem, so here it is.

we have the same question here.
However, that does not answer my question below.

I’m using SQL Server 2012 and trying to implement transactional replication. I’m using the system stored procedures to create the publications and subscriptions. I was successful in creating these things, but when i check the replication monitor, it shows “Uninitialized subscription”.

I have 2 PUBLISHER SERVERS:

publisher1 and publisher2

within these 2 publisher servers I need to publish all the databases that come out of the following query:

select 'USE ' + NAME  from sys.databases
where name like '__OrderPaymentOnline'

these database would be:

USE USOrderPaymentOnline
USE FROrderPaymentOnline
USE EUOrderPaymentOnline
USE DEOrderPaymentOnline

for publisher1
and

USE AUOrderPaymentOnline
USE UKOrderPaymentOnline
USE ATOrderPaymentOnline

for publisher2.

for each of these databases I will need to publish the tables that come out as a result of this query:

   select name from sys.tables
   where name like '%OrderPaymentOnline%'

which gives me the following 3 tables:

OrderPaymentOnlineAuthorisationError
OrderPaymentOnline
OrderPaymentOnlineAuthorisation

I achieve this through using these 2 scripts below:

(1) — for FROrderPaymentOnline ONLY

--====================================================================================
-- when the database has not been published, we need to publish it before anything else
-- marcelo miorelli
--09-jan-2015


--In all XXOrderPaymentOnline databases:


--replication passwords


[email protected]_DOMAIN
--kr1shn4


[email protected]_test_DOMAIN
--r4dh4


--SQLReplication
--my_password_

--====================================================================================



--====================================================================================
--get a list of the databases to be published in the current server
--====================================================================================

PRINT @@SERVERNAME
--publisher1

select NAME  from sys.databases
where name like '__OrderPaymentOnline'


--publisher1
--USOrderPaymentOnline
--FROrderPaymentOnline
--EUOrderPaymentOnline
--DEOrderPaymentOnline




--====================================================================================
-- publishing the FROrderPaymentOnline DB
-- server is 
--publisher1
-- it is a TEST server
-- the account and password have been used.
--====================================================================================

PRINT @@SERVERNAME
--publisher1


use FROrderPaymentOnline
go

-- get the distributor
sp_get_distributor
--SQLDISTLON1

--==================================================================================================
--make sure the @Login DEV\SQLReplication
-- is a user in the publisher database
-- and as a minimum level of permissions he is a DB_OWNER 
--==================================================================================================
USE [FROrderPaymentOnline]
GO
CREATE USER [DEV\SQLReplication] FOR LOGIN [DEV\SQLReplication]
ALTER USER [DEV\SQLReplication] WITH DEFAULT_SCHEMA=[dbo]
--==================================================================================================
--The publisher login must be a member of the 'db_owner' role at the publisher database 
--when generating a regular snapshot.  
--==================================================================================================
EXEC sp_addrolemember N'db_owner', N'DEV\SQLReplication'
GO



DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'FROrderPaymentOnline'; 
SET @publication = N'FROrderPaymentOnline'; 

-- Windows account used to run the Log Reader and Snapshot Agents.
SET @login = 'DEV\SQLReplication'
-- This should be passed at runtime.
SET @password = 'my_password_'; 

-- Enable transactional or snapshot replication on the publication database.
EXEC sp_replicationdboption 
    @[email protected], 
    @optname=N'publish',
    @value = N'true';

-- Execute sp_addlogreader_agent to create the agent job. 
-- to run sp_addlogreader_agent you better be in the publicationDB
EXEC sp_addlogreader_agent 
    @job_login = @login, 
    @job_password = @password,
    -- Explicitly specify the use of Windows Integrated Authentication (default) 
    -- when connecting to the Publisher.
    @publisher_security_mode = 1;

-- Create a new transactional publication with the required properties. 
EXEC sp_addpublication 
    @publication = @publication, 
    @status = N'active',
    @allow_push = N'true',
    @allow_pull = N'true',
    @independent_agent = N'true';

-- Create a new snapshot job for the publication, using a default schedule.
EXEC sp_addpublication_snapshot 
    @publication = @publication, 
    @job_login = @login, 
    @job_password = @password,
    -- Explicitly specify the use of Windows Integrated Authentication (default) 
    -- when connecting to the Publisher.
    @publisher_security_mode = 1;
GO

(2) — for FROrderPaymentOnline ONLY

--========================================================================
--Replication
--Configure replication for the following tables:
--• OrderPaymentOnline
--• OrderPaymentOnlineAuthorisation
--• OrderPaymentOnlineAuthorisationError

--In all XXOrderPaymentOnline databases:


--replication passwords


[email protected]_DOMAIN
--kr1shn4


[email protected]_test_DOMAIN
--r4dh4


--SQLReplication
--my_password_


-- Marcelo Miorelli 
-- 09-Jan-2015
--========================================================================

select 'USE ' + NAME  from sys.databases
where name like '__OrderPaymentOnline'


PRINT @@SERVERNAME

--SQLWEBLON1\INSTSQLWEB1 -- in test
--USE USOrderPaymentOnline
USE FROrderPaymentOnline
--USE EUOrderPaymentOnline
--USE DEOrderPaymentOnline


--========================================================================
-- WHERE IS THE TABLE REPLICATED?
--========================================================================

USE FROrderPaymentOnline
GO

SELECT 
        Publication=P.name ,
        TableName = A.name ,
        DestinationTable = A.dest_table  
        --,p.*
        --,a.*

FROM
syspublications P 
INNER JOIN sysarticles A ON P.pubid = A.pubid

WHERE a.name like'%OrderPayment%'



--========================================================================
-- CHECK WHETHER THE TABLES TO BE ADDING TO REPLICATION HAVE PRIMARY KEY
-- THEY NEED TO HAVE a primary key in order to be added to transactional replication
--========================================================================

USE FROrderPaymentOnline
GO

SELECT A.NAME
      ,A.IS_PUBLISHED 
      ,A.IS_REPLICATED
      ,A.HAS_REPLICATION_FILTER
      ,A.IS_MERGE_PUBLISHED
      ,I.NAME
      ,I.TYPE_DESC
      ,I.IS_PRIMARY_KEY
      ,I.IS_UNIQUE_CONSTRAINT
 FROM SYS.TABLES A
 LEFT OUTER JOIN SYS.INDEXES I
              ON A.OBJECT_ID = I.OBJECT_ID
WHERE a.name like'%OrderPayment%'



 --========================================================================
-- for the tables that dont have a primary key 
-- look at the table definition and create a primary key
--========================================================================
   SP_HELP 'OrderPaymentOnline'
   SP_HELP 'OrderPaymentOnlineAuthorisation'
   SP_HELP 'OrderPaymentOnlineAuthorisationError'

   --example:
   --ALTER TABLE OrderPayment
   --ADD CONSTRAINT PK_OrderPayment PRIMARY KEY CLUSTERED (OrderPaymentId DESC) ON [PRIMARY]

--========================================================================
-- ADDING TABLES TO REPLICATION


--http://dba.stackexchange.com/questions/57600/how-to-add-an-article-to-existing-replication

--Adding an a new article involves 
-- 1) adding the article to the publication, 
-- 2) creating a new snapshot, and 
-- 3) synchronizing subscriptions to apply the schema and data for the newly added articles. 

-- Reinitialization is not required, but a new snapshot is.

--Your steps are correct if the publication only has existing push subscriptions. 

--If the publication has existing pull subscriptions, 
--you will also need to execute sp_refreshsubscriptions. 

--And you are correct, to generate a new snapshot you can use the View Snapshot Agent dialog, 
--or you can execute sp_startpublication_snapshot.

--To avoid generating a full snapshot when adding a new article:
--publication properties @immediate_sync and @allow_anonymous must be set to 0. 

--Execute sp_addarticle, then 
--execute sp_addsubscription. 

--If the publication has existing pull subscriptions, 
--you must also call sp_refreshsubscriptions. 

--Then generate a snapshot and only a snapshot for the newly added article will be generated. 

--Note that this approach is not necessary but is typically used 
--when Administrators want to avoid generating a full snapshot when adding a new article.

--========================================================================


USE FROrderPaymentOnline
GO

EXEC sp_addarticle @publication = N'FROrderPaymentOnline', 
                   @article = N'OnlinePaymentProvider',
                   @source_table=N'OnlinePaymentProvider'
GO



EXEC sp_addarticle @publication = N'FROrderPaymentOnline', 
                   @article = N'OrderPaymentOnline',
                   @source_table=N'OrderPaymentOnline'
GO

EXEC sp_addarticle @publication = N'FROrderPaymentOnline', 
                   @article = N'OrderPaymentOnlineAuthorisation',
                   @source_table=N'OrderPaymentOnlineAuthorisation'
GO

EXEC sp_addarticle @publication = N'FROrderPaymentOnline', 
                   @article = N'OrderPaymentOnlineAuthorisationError',
                   @source_table=N'OrderPaymentOnlineAuthorisationError'
GO


--========================================================================
-- GET A LIST OF SUBSCRIBERS
-- if the subscribers are already there
-- NO NEED TO CREATE THEM
-- Marcelo Miorelli
-- 17-nov-2014
--========================================================================

USE FROrderPaymentOnline
GO

select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 


--=============================================================================================
-- Only add the subscribers if they don't exist
-- CREATE THE SUBSCRIPTION
--=============================================================================================

-- this is run on the publisher
use [FROrderPaymentOnline]
exec sp_addsubscription @publication = N'FROrderPaymentOnline', 
                        @subscriber = N'REPLON1', 
                        @destination_db = N'FROrderPaymentOnline', 
                        @sync_type = N'Automatic', 
                        @subscription_type = N'pull', 
                        @update_mode = N'read only'
GO


-- Execute this batch at the Subscriber.
-- run at the subscriber -- REPLON1

--=============================================================================================
-- the subscription DB (in this case FROrderPaymentOnline ) must already be created!!
--=============================================================================================

select @@servername
-- replon1.MY_test_DOMAIN


use FROrderPaymentOnline
go
exec sp_addpullsubscription @publisher = N'SQLWEBLON1\INSTSQLWEB1', 
@publication = N'FROrderPaymentOnline', 
@publisher_db = N'FROrderPaymentOnline', 
@independent_agent = N'True', 
@subscription_type = N'pull', 
@description = N'publication FROrderPaymentOnline - create by MM on 09-jan-2015',
@update_mode = N'read only', 
@immediate_sync = 0

use [FROrderPaymentOnline]
go
exec sp_addpullsubscription_agent 
@publisher = N'SQLWEBLON1\INSTSQLWEB1', 
@publication = N'FROrderPaymentOnline', 
@publisher_db = N'FROrderPaymentOnline', 
@distributor = N'SQLDISTLON1', 
@distributor_security_mode = 1, 
@distributor_login = N'dev\SQLReplication', 
@distributor_password = 'r4dh4', 
@enabled_for_syncmgr = N'False', 
@frequency_type = 64, 
@frequency_interval = 0, 
@frequency_relative_interval = 0, 
@frequency_recurrence_factor = 0, 
@frequency_subday = 0, 
@frequency_subday_interval = 0, 
@active_start_time_of_day = 0, 
@active_end_time_of_day = 235959,
@active_start_date = 0, 
@active_end_date = 0, 
@alt_snapshot_folder = N'', 
@working_directory = N'', 
@use_ftp = N'False', 
@job_login = N'dev\SQLReplication',
@job_password = 'r4dh4',
@publication_type = 0
GO

--========================================================================
-- now come back to the publisher server
--========================================================================
select @@servername
-- sqlweblon1.MY_test_DOMAIN
use [FROrderPaymentOnline]


--========================================================================
-- GET A LIST OF SUBSCRIBERS
-- if the subscribers are already there
-- NO NEED TO CREATE THEM
-- Marcelo Miorelli
-- 17-nov-2014
--========================================================================

USE FROrderPaymentOnline
GO

select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 


--========================================================================
-- Only add the subscribers if they don't exist
--========================================================================

USE FROrderPaymentOnline
GO

EXEC sp_addsubscription
  @publication = N'FROrderPaymentOnline',
  @subscriber = N'REPLON1',
  @article = N'OrderPaymentOnline',
  @update_mode=N'read only',
  @frequency_type=64,
  @sync_type=N'automatic',
  @destination_db = N'FROrderPaymentOnline',
  @subscription_type = N'pull',
  @status = N'subscribed'
GO

EXEC sp_addsubscription
  @publication = N'FROrderPaymentOnline',
  @subscriber = N'REPLON1',
  @article = N'OrderPaymentOnlineAuthorisation',
  @update_mode=N'read only',
  @frequency_type=64,
  @sync_type=N'automatic',
  @destination_db = N'FROrderPaymentOnline',
  @subscription_type = N'pull',
  @status = N'subscribed'
GO

EXEC sp_addsubscription
  @publication = N'FROrderPaymentOnline',
  @subscriber = N'REPLON1',
  @article = N'OrderPaymentOnlineAuthorisationError',
  @update_mode=N'read only',
  @frequency_type=64,
  @sync_type=N'automatic',
  @destination_db = N'FROrderPaymentOnline',
  @subscription_type = N'pull',
  @status = N'subscribed'
GO

--========================================================================
-- REFRESH THE SUBSCRIPTIONS
--========================================================================

USE FROrderPaymentOnline
GO

EXEC sp_refreshsubscriptions @publication = N'FROrderPaymentOnline'
GO


--========================================================================
-- Start the Snapshot Agent job.
--========================================================================
USE FROrderPaymentOnline
GO
EXEC sp_startpublication_snapshot @publication = N'FROrderPaymentOnline'
go


--========================================================================
-- only if you need a clear start
-- on both the publisher and subscriber
--========================================================================
USE master
EXEC sp_removedbreplication @dbname=N'FROrderPaymentOnline'
GO
USE FROrderPaymentOnline
GO
sp_dropsubscription @subscriber='REPLON1'
GO
sp_droppublication N'FROrderPaymentOnline'

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I found out what the problem was, just a TYPO in the servername/instancename.

These scripts work fine to create a subscription in a sql server (2012-2008-2005) transactional replication.

I would like to leave the question here though, for future reference, unless Moderators prefer me not to. In that case please feel free to remove it or just let me know.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply