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

Replication porblem

Hi everybody,

i got this error message while i'm trying to execute my replication job.

"error 4813"

Expected the text length in data stream for bulk copy of
text, ntext, or image data."

Does anybody have an idea about it?
thanks

Let me know if the workarounds in the below link do not work for you.

http://www.starquest.com/Supportdocs/techSQDR/SQV00DR0013_BCPError.html

|||It seems to work! Thanks
I wonder if there is a Microsoft's KB article about it, 'cause i didn't find any!!

Replication jobs owned by sa

The reason sa is often suggested as the owner of
replication jobs is that when the job runs, there is an
active directory lookup performed of the job owner. This
is the reason sa is used - no AD lookup required. The
jobs themselves are run under the authority of the sql
server agent so trusted/non-trusted security doesn't
really apply, as windows logins are always authenticated.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
So are you saying , that even when i change the authentication mode to
Windows only , the jobs will still run even if they are owned by sa
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:170001c4e8d4$3eea74c0$a501280a@.phx.gbl...
> The reason sa is often suggested as the owner of
> replication jobs is that when the job runs, there is an
> active directory lookup performed of the job owner. This
> is the reason sa is used - no AD lookup required. The
> jobs themselves are run under the authority of the sql
> server agent so trusted/non-trusted security doesn't
> really apply, as windows logins are always authenticated.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Correct. The owner of the job is not the login used to
run it, as this is a cmdexec type of job and therefore
runs as the agent.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

replication job failure

Our SQl Server 2000 was rebooted recently but when I check the management
section then jobs replication its registered as failed yet when checking the
history it is still executing step 2.
Yet when I check in the replication monitor in the distribution agents
replication is as normal do you know how I can get rid of the fail status if
it is still processing transaction replication that has been running for 3
days on step 2?
thanks for any help
Sammy
Sammy,
you could try stopping and restarting the job, then refresh.
Rgds,
Paul Ibison
|||Thanks Paul it worked
"Paul Ibison" wrote:

> Sammy,
> you could try stopping and restarting the job, then refresh.
> Rgds,
> Paul Ibison
>
>

Replication Job Failed. Unable to determine the owner

A former employee at my organization created a replication job to back up our database. The developer has since left and his windows account was deleted. I'm now receiving this error:

SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-05 15:40:00 - Message: The job failed. Unable to determine if the owner (username) of job Replication agents checkup has server access (reason: Could not obtain information about Windows NT group/user 'username', error code 0x534. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).

How can I fix this? I looked under the Replication folder but didn't see anything.

In Object Explorer, look at the job in SQL Server Agent to see who owns it, and verify the account is valid.

|||That's for the help. I changed the owner but am now getting this error:

SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-07 01:00:00 - Message: The job failed. The Job was invoked by Schedule 175 (Replication agent schedule.). The last step to run was step 1 (Run agent.).

When I executed the maintenance plan manually it worked, but it doesn't seem to work on the schedule. I've never created a maintenance plans so I'm going to look up some more information to see if I can figure out what is going to wrong.

Replication Job Failed. Unable to determine the owner

A former employee at my organization created a replication job to back up our database. The developer has since left and his windows account was deleted. I'm now receiving this error:

SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-05 15:40:00 - Message: The job failed. Unable to determine if the owner (username) of job Replication agents checkup has server access (reason: Could not obtain information about Windows NT group/user 'username', error code 0x534. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).

How can I fix this? I looked under the Replication folder but didn't see anything.

In Object Explorer, look at the job in SQL Server Agent to see who owns it, and verify the account is valid.

|||That's for the help. I changed the owner but am now getting this error:

SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-07 01:00:00 - Message: The job failed. The Job was invoked by Schedule 175 (Replication agent schedule.). The last step to run was step 1 (Run agent.).

When I executed the maintenance plan manually it worked, but it doesn't seem to work on the schedule. I've never created a maintenance plans so I'm going to look up some more information to see if I can figure out what is going to wrong.

Replication job does not end - even when its idle on the server

I am the two stored procedures below, used in a similar way, to add a
subscription. But for some reason, the replication completes but it does not
stop. If I add a subscription using Enterprise manager (EM), the job does
stop. I scripted out the subscription that EM had created and noticed that
extra code had been added to create the replication jobs (ie. if you generate
the script for a job, there is an option to "script creation of replication
jobs"). Do I need to do this too?
Any help would be very much appreciated.
ServerName...../
use NewTest
GO
exec sp_addpullsubscription @.publisher = @.@.ServerName, @.publisher_db =
'pubs',
@.publication = 'pubs', @.independent_agent = 'true',
@.subscription_type = 'anonymous', @.update_mode = 'read only',
@.immediate_sync = 1
GO
exec sp_addpullsubscription_agent @.publisher = @.@.ServerName, @.publisher_db =
'pubs', @.publication = 'pubs', @.distributor = @.@.ServerName,
@.subscriber_security_mode = 1, @.distributor_security_mode = 1, @.use_ftp =
'true', @.publication_type = 1
GO
/..... End: Script to be run at Subscriber: @.@.ServerName ...../
What does the replication job which does not stop say?
Scripting replication jobs is not necessary unless you have customized them.
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
""Julian Reed via droptable.com"" <forum@.droptable.com> wrote in message
news:50DAB174F7810@.droptable.com...
> I am the two stored procedures below, used in a similar way, to add a
> subscription. But for some reason, the replication completes but it does
not
> stop. If I add a subscription using Enterprise manager (EM), the job does
> stop. I scripted out the subscription that EM had created and noticed
that
> extra code had been added to create the replication jobs (ie. if you
generate
> the script for a job, there is an option to "script creation of
replication
> jobs"). Do I need to do this too?
> Any help would be very much appreciated.
> ServerName...../
> use NewTest
> GO
> exec sp_addpullsubscription @.publisher = @.@.ServerName, @.publisher_db =
> 'pubs',
> @.publication = 'pubs', @.independent_agent = 'true',
> @.subscription_type = 'anonymous', @.update_mode = 'read only',
> @.immediate_sync = 1
> GO
> exec sp_addpullsubscription_agent @.publisher = @.@.ServerName, @.publisher_db
=
> 'pubs', @.publication = 'pubs', @.distributor = @.@.ServerName,
> @.subscriber_security_mode = 1, @.distributor_security_mode = 1, @.use_ftp =
> 'true', @.publication_type = 1
> GO
> /..... End: Script to be run at Subscriber: @.@.ServerName ...../

Replication Job Cleanup

Over the past couple of years, I have added & removed publications &
subscriptions, usung the wizards the come with SQL 2000. Recently we
had a problem with the dBs getting backed up and when I peered into the
SQL Server Agent->Jobs folder, there were many many more jobs than I
had publications & subscriptions, in fact some for tables that no
longer existed.
So my question is this. Is there a SP somewhere that will clean up all
the 'old' stuff without messing up all the stuff that is currently
working.
AHIA,
Larry...
No. For the most part these objects consume very little space and
replication won't reuse them or get confused by their existence.
This being said you can query sysmergearticles and get an idea of what is
currently in use on your publisher/subscriber. Again I would be adverse to
touching a production system in case I whack something - unless of course I
am a consultant as this might mean considerable overtime for me when things
go south.
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
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1130949038.398739.282220@.g44g2000cwa.googlegr oups.com...
> Over the past couple of years, I have added & removed publications &
> subscriptions, usung the wizards the come with SQL 2000. Recently we
> had a problem with the dBs getting backed up and when I peered into the
> SQL Server Agent->Jobs folder, there were many many more jobs than I
> had publications & subscriptions, in fact some for tables that no
> longer existed.
> So my question is this. Is there a SP somewhere that will clean up all
> the 'old' stuff without messing up all the stuff that is currently
> working.
> AHIA,
> Larry...
>
|||Hillary,
What are you saying? What are you implying? Would a consultant
really do that? Who me? Never!!!!
As always, thanks for your impartial advice!!!
Larry...