I have a TSQL script to add daily tables to replication and then run
the snapshot agent to distribute them to two subscribers. The script
executes without errors, but when I check the running jobs for each
server I see the following:

JUST AN EXAMPLE
Job1 'Category' REPL-Snapshot
Job2 'Category' REPL-Distribution

What is the difference between these two categories? Also 'Job1' works
properly and receives the 3 new replicated tables, while 'Job2' seems
to be stuck on Step 2 and isn't receiving the 3 new replicated tables.

Below is a copy of the stored procedure for reference.

GO
SET QUOTED_IDENTIFIER OFF

GO
SET ANSI_NULLS OFF

GO
CREATE PROCEDURE dbo.sp_TESTaddDailyTablesToReplication (@@IOI_TABLE
varchar(64), @@TRADE_TABLE varchar(64), @@CHAT_TABLE varchar(64) ) AS

DECLARE @SUBSCRIBER_NYPROD2 varchar(64)
DECLARE @SUBSCRIBER_CTDEV2 varchar(64)

DECLARE @SP_INSERT_PREFIX varchar(24)
DECLARE @SP_DELETE_PREFIX varchar(24)
DECLARE @SP_UPDATE_PREFIX varchar(24)

DECLARE @INSERT_SP varchar(24)
DECLARE @DELETE_SP varchar(24)
DECLARE @UPDATE_SP varchar(24)

SET @SUBSCRIBER_NYPROD2 = 'INDII_NY2_PROD'
SET @SUBSCRIBER_CTDEV2 = 'D02'

SET @SP_INSERT_PREFIX = 'CALL sp_MSins_'
SET @SP_DELETE_PREFIX = 'CALL sp_MSdel_'
SET @SP_UPDATE_PREFIX = 'CALL sp_MSupd_'

SET @INSERT_SP = @SP_INSERT_PREFIX + @@IOI_TABLE
SET @DELETE_SP = @SP_DELETE_PREFIX + @@IOI_TABLE
SET @UPDATE_SP = @SP_UPDATE_PREFIX + @@IOI_TABLE

DECLARE @SCHEMA_OPTIONS int
SET @SCHEMA_OPTIONS = 0x000000000000CEA3
exec sp_addarticle @publication = N'Indii', @article = @@IOI_TABLE,
@source_owner = N'dbo', @source_object = @@IOI_TABLE,
@destination_table = @@IOI_TABLE, @type = N'logbased', @creation_script
= null, @description = null, @pre_creation_cmd = N'drop',
@schema_option = @SCHEMA_OPTIONS, @status = 16, @vertical_partition =
N'false', @ins_cmd = @INSERT_SP, @del_cmd = @DELETE_SP, @upd_cmd =
@UPDATE_SP, @filter = null, @sync_object = null, @auto_identity_range =
N'false'
exec sp_addsubscription @publication = N'Indii', @article =
@@IOI_TABLE, @subscriber = @SUBSCRIBER_NYPROD2, @destination_db =
N'Indii', @sync_type = N'automatic', @update_mode = N'read only',
@offloadagent = 0, @dts_package_location = N'distributor'
exec sp_addsubscription @publication = N'Indii', @article =
@@IOI_TABLE, @subscriber = @SUBSCRIBER_CTDEV2, @destination_db =
N'Indii', @sync_type = N'automatic', @update_mode = N'read only',
@offloadagent = 0, @dts_package_location = N'distributor'

SET @INSERT_SP = @SP_INSERT_PREFIX + @@TRADE_TABLE
SET @DELETE_SP = @SP_DELETE_PREFIX + @@TRADE_TABLE
SET @UPDATE_SP = @SP_UPDATE_PREFIX + @@TRADE_TABLE

exec sp_addarticle @publication = N'Indii', @article = @@TRADE_TABLE,
@source_owner = N'dbo', @source_object = @@TRADE_TABLE,
@destination_table = @@TRADE_TABLE, @type = N'logbased',
@creation_script = null, @description = null, @pre_creation_cmd =
N'drop', @schema_option =@SCHEMA_OPTIONS, @status = 16,
@vertical_partition = N'false', @ins_cmd = @INSERT_SP, @del_cmd =
@DELETE_SP, @upd_cmd = @UPDATE_SP, @filter = null, @sync_object = null,
@auto_identity_range = N'false'
exec sp_addsubscription @publication = N'Indii', @article =
@@TRADE_TABLE, @subscriber = @SUBSCRIBER_NYPROD2, @destination_db =
N'Indii', @sync_type = N'automatic', @update_mode = N'read only',
@offloadagent = 0, @dts_package_location = N'distributor'
exec sp_addsubscription @publication = N'Indii', @article =
@@TRADE_TABLE, @subscriber = @SUBSCRIBER_CTDEV2, @destination_db =
N'Indii', @sync_type = N'automatic', @update_mode = N'read only',
@offloadagent = 0, @dts_package_location = N'distributor'

SET @INSERT_SP = @SP_INSERT_PREFIX + @@CHAT_TABLE
SET @DELETE_SP = @SP_DELETE_PREFIX + @@CHAT_TABLE
SET @UPDATE_SP = @SP_UPDATE_PREFIX + @@CHAT_TABLE

exec sp_addarticle @publication = N'Indii', @article = @@CHAT_TABLE,
@source_owner = N'dbo', @source_object = @@CHAT_TABLE,
@destination_table = @@CHAT_TABLE, @type = N'logbased',
@creation_script = null, @description = null, @pre_creation_cmd =
N'drop', @schema_option =@SCHEMA_OPTIONS, @status = 16,
@vertical_partition = N'false', @ins_cmd = @INSERT_SP, @del_cmd =
@DELETE_SP, @upd_cmd = @UPDATE_SP, @filter = null, @sync_object = null,
@auto_identity_range = N'false'
exec sp_addsubscription @publication = N'Indii', @article =
@@CHAT_TABLE, @subscriber = @SUBSCRIBER_NYPROD2, @destination_db =
N'Indii', @sync_type = N'automatic', @update_mode = N'read only',
@offloadagent = 0, @dts_package_location = N'distributor'
exec sp_addsubscription @publication = N'Indii', @article =
@@CHAT_TABLE, @subscriber = @SUBSCRIBER_CTDEV2, @destination_db =
N'Indii', @sync_type = N'automatic', @update_mode = N'read only',
@offloadagent = 0, @dts_package_location = N'distributor'

DECLARE @SNAPSHOT_JOB_NAME varchar(64)

-- Run Snapshot for NY2 server
SET @SNAPSHOT_JOB_NAME = 'INNYWPP01\PRODUCTION-Indii-Indii-1'
EXEC msdb.dbo.sp_start_job @job_name = @SNAPSHOT_JOB_NAME
-- Run Snapshot for CT2 serve
SET @SNAPSHOT_JOB_NAME = 'innywpp01\production-Indii-Indii-D02-3'
EXEC msdb.dbo.sp_start_job @job_name = @SNAPSHOT_JOB_NAME

PRINT 'added ' + @@IOI_TABLE + ' from replication'
PRINT 'added ' + @@TRADE_TABLE + ' from replication'
PRINT 'added ' + @@CHAT_TABLE + ' from replication'
GO
SET QUOTED_IDENTIFIER OFF

GO
SET ANSI_NULLS ON
GO

job1 is the snapshot job which generates the snapshot. The snapshot contains
the schema, data, procs, and metadata required to build the article/table on
the subscriber.

job 2 is the distribution job which distributes the snapshot and all
changes.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

<war_wheelan (AT) yahoo (DOT) com> wrote