Replication Scripts Stopped Working..

I have a merge publication that I always create with a script but has now stopped working with what appears to be a complete server problem.

The actual command i am executing is a simple

sp_addpublication_snapshot

but it fails with

Msg 50000, Level 16, State 1, Procedure sp_add_jobstep_internal, Line 253

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Msg 3931, Level 16, State 1, Procedure sp_MSadd_repl_job_unsafe, Line 376

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

I have tried recreating the database from scratch, renaming it, renaming the publication, even disabling the publications altogether but it makes no difference. There is nothing in the sql or event logs and I cant do any work until Ive got past it - help!

I am using SQL 2005 SP2 if that makes any difference.

Thanks

Andy

It looks like you have an uncommitted transaction hanging around. Open a query window and type:

Commit Tran

GO

It will either tell you that there are no transactions to commit or commit the uncomplted transaction. At that point, re-run your script.

Hope this helps...

Scott

Replication Scripts Stopped Working..

I have a merge publication that I always create with a script but has now stopped working with what appears to be a complete server problem.

The actual command i am executing is a simple

sp_addpublication_snapshot

but it fails with

Msg 50000, Level 16, State 1, Procedure sp_add_jobstep_internal, Line 253

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Msg 3931, Level 16, State 1, Procedure sp_MSadd_repl_job_unsafe, Line 376

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

I have tried recreating the database from scratch, renaming it, renaming the publication, even disabling the publications altogether but it makes no difference. There is nothing in the sql or event logs and I cant do any work until Ive got past it - help!

I am using SQL 2005 SP2 if that makes any difference.

Thanks

Andy

It looks like you have an uncommitted transaction hanging around. Open a query window and type:

Commit Tran

GO

It will either tell you that there are no transactions to commit or commit the uncomplted transaction. At that point, re-run your script.

Hope this helps...

Scott

Replication scripts for 2000 same as 2005 ?

I am curious to know if i script the replication scripts for creating
publisher, publication,etc on 2000, can i safely run them on sql 2005 or are
there some changes to the metadata ?
Thanks
Yes you can run them no problem.
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
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23hK0gj8yHHA.1208@.TK2MSFTNGP03.phx.gbl...
>I am curious to know if i script the replication scripts for creating
>publisher, publication,etc on 2000, can i safely run them on sql 2005 or
>are there some changes to the metadata ?
> Thanks
>
|||Have a look in BOL at : "How to: Upgrade Replication Scripts".
The original ones should work OK but the recommendation is to make changes
to the security of the jobs.
Cheers,
Paul Ibison
sql

replication script

Hi All:
I set up a merge replication on my own local computer and works fine, so i
generated a script and tried to run this script in another computer ,which
already has a replication. (i change the server name before running script)
but still got two errors:
Server: Msg 14099, Level 16, State 1, Procedure sp_adddistributor, Line 72
The server 'repl_distributor' is already defined as a Distributor.
The dependent aliases were mapped to the new database owner.
Database owner changed.
Server: Msg 14074, Level 16, State 1, Procedure sp_adddistpublisher, Line
196
The server 'NICK' is already listed as a Publisher.
but the my replication still works even i got two errors when run script.
so how can i change my script to avoid those two errors when running script?
Cheers
Nick
Nick,
comment out the calls to sp_adddistributor and sp_adddistpublisher.
Rgds,
Paul Ibison
|||Thanks Paul.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#iJ4CmXCFHA.560@.TK2MSFTNGP15.phx.gbl...
> Nick,
> comment out the calls to sp_adddistributor and sp_adddistpublisher.
> Rgds,
> Paul Ibison
>

Replication Question about job names and categories

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@.yahoo.com> wrote in message
news:1110835060.331561.184840@.f14g2000cwb.googlegr oups.com...
> 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
>

Replication Question about job names and categories

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<war_wheelan@.yahoo.com> wrote in message
news:1110817809.086867.107280@.l41g2000cwc.googlegr oups.com...
>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.

<snip
I have no idea myself, but in general you'll probably get a better response
to replication questions if you post in
microsoft.public.sqlserver.replication.

Simon