Replication Snapshot Incorrect Parameter

After using the wizard to create a very simple snapshot. I keep receiving this message:

Replication-Replication Snapshot Subsystem: agent D700PT106-gdalcd_10-test-3 failed. The parameter is incorrect. The step failed.

Anybody have any idea what this means.

I need to ask you a few questions before I can troubleshoot this further:

1) Is this SQL2000 or SQL2005?

2) Is this a clustered system?

3) Are there anything interesting in the SQLServerAgent log?

-Raymond

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 question

Dear Friends,
i'm facing a strange thing with merge replication , the
merge agent is running and working successfully
displaying the message 'no data needed to be merged'
although alot of modificatins is been made to the
subscriber. and i stoped the agent and run it again , same
message.
any idea might cause this problem
Thanks
Scroll to the right. There are numbers out there for the volume of
inserts/updates/deletes being moved. What you are seeing is completely
normal since you are seeing the final state after it has run and already
moved data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Replication problem (please help if you can)

Since our outsourced database team made some changes to the database we
are getting the following from the Distribution Agent Error Details:
{call sp_MSget_repl_commands(5, ?, 0, 7500000)}
Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__7AB9340C'.
Cannot insert duplicate key in object '#79C50FD3'.
Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__7AB9340C'.
Cannot insert duplicate key in object '#79C50FD3'.
(Source: INTRANETSVR (Data source); Error number: 2627)
----
--
The it's a transactional replica that has been running happily for some
time now, and I am only a db admin with very little experience in
trouble shooting.
thanks in advance.
Kev
kevintomlinson@.hotmail.comOn the destination database run
select object_name(79C50FD3)
That should give you the name of the table that's having the problem.
If you are pushing from the distributor (simplest to administer) then look
at the distribution agent - it should be showing an error.
If you restart it then it should give the same error. I think you can get he
command from one of the windows there but I usually use the profiler on the
subscriber, start the agent and the last instruction recorded will be the on
e
in error.
Then you have to find out why this is causing a problem.
Could be that the index on the destination is not correct and doesn't match
the source (just change it) ot the destination data could be out of step wit
h
the source.
If that's the case then you have a choice between changing the data on the
destination of re-snapshotting (just the table if possible - depends on your
settup).
"Kev" wrote:

> Since our outsourced database team made some changes to the database we
> are getting the following from the Distribution Agent Error Details:
>
> {call sp_MSget_repl_commands(5, ?, 0, 7500000)}
> Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__7AB9340C'.
> Cannot insert duplicate key in object '#79C50FD3'.
> Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__7AB9340C'.
> Cannot insert duplicate key in object '#79C50FD3'.
> (Source: INTRANETSVR (Data source); Error number: 2627)
> ----
--
> The it's a transactional replica that has been running happily for some
> time now, and I am only a db admin with very little experience in
> trouble shooting.
> thanks in advance.
> Kev
> kevintomlinson@.hotmail.com
>

replication of views that reference other views

hi,
in a merge replication I use a view (V1) that references another view (V2)
in its select statement.
when running the merge agent to initialize the subscriber I get this error:
The schema script '\\SSS-SERVER\ReplData\unc\SSS-
SERVER_E5K_714_FS_E5K_714_FS_Replikat\200404072145
58\V1_667.sch' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
Unable to replicate a view or function because the referenced objects or
columns are not present on the Subscriber.
(Source: SSS1 (Agent); Error number: 0)
Invalid object name 'dbo.V2'.
(Source: SSS1 (Data source); Error number: 208)
I think the error occurs, because V2 is not yet present on the subscriber
when the agent tries to create V1. (when i check the views created on the
subscriber after the agent fails, V2 is missing)
so how can I tell the agent the order of creating objects?
or is there any other solution?
thanks for your help
thomas
Thomas,
your dependencies are out of sync. Run sp_refreshview on the 2 views then
they'll be replicates in the correct order.
Regards,
Paul Ibison
|||thanks paul,
i tried sp_refreshview on all my views, but i still have the same problem.
thomas
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OAYVRyjHEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Thomas,
> your dependencies are out of sync. Run sp_refreshview on the 2 views then
> they'll be replicates in the correct order.
> Regards,
> Paul Ibison
>
|||use a presnapshot script that precreates the views on the subscriber.
"Thomas Schnauer" <thomas@.schoenauer.at> wrote in message
news:4076c17c$0$30734$91cee783@.newsreader02.highwa y.telekom.at...
> thanks paul,
> i tried sp_refreshview on all my views, but i still have the same problem.
> thomas
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:OAYVRyjHEHA.1944@.TK2MSFTNGP11.phx.gbl...
then
>
|||Thomas,
Although my reply may not apply to you, I am posting some documentation that
may help others.
Contrary to table articles, order of views in publication is seen to be
controlled differently. Maybe this behaviour has been fixed in a later
service pack or hotfix that I am not aware of, so sorry if that is the case.
Unless it is too late or at all feasible, try this workaround:
1. Create a publication for the views.
2. Add one view and generate a snapshot (this step is key to successful
implementation of this workaround).
3. Continue step 2 until all the views have been added. The order in which
you add your views will determine the order in which the snapshot will apply
them to the subscribers.
4. Apply the snapshot. This should work fine now.
Now, the tricky part here is that if you have to change your views and
dependencies change, you might be in trouble again. For example, View2
depends on View1. You add View1, gen snapshot, add View2, gen snapshot and
finally apply snapshot. Later you create View3 and change View2 to include
reference to View3. If you now add View3 to the publication and reinitialise
your subscriptions, the snapshot will try to apply View1, View2 and View3 in
that order instead of the order View1, View3, View2 as required. Obviously,
the agent will fail with the same message you have posted because View3 does
not exist at the subscriber yet and View2 is being applied.
So, the bottom line is: do not use publications with views as articles if
you can have the above scenario. But if your design is not going to change
at all (very unlikely?), then go ahead with the above workaround.
Alternatively, use pre-snapshot scripts, as also suggested by Hilary. Of
course, the scripts should contain the views in the proper order.
Hope the above helps,
Raj Moloye
|||Thomas,
the order of running it is crucial - run sp_refreshview on the simple view
then run it on the view that depends on it. This dependency should show up
using sp_depends. Provided the dependency is recognised, then the order of
application in the snapshot should be correct. I have run this successfully
before. However, if it doesn't work then Hilary's advice (postscripts) or
sp_addscriptexec will do the same thing.
Regards,
Paul Ibison
|||views can also be replicated to all subscribers using sp_addscriptexec
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eMlshkkHEHA.3576@.TK2MSFTNGP10.phx.gbl...
> use a presnapshot script that precreates the views on the subscriber.
> "Thomas Schnauer" <thomas@.schoenauer.at> wrote in message
> news:4076c17c$0$30734$91cee783@.newsreader02.highwa y.telekom.at...
problem.
> then
>
|||Paul,
you were right. I re-ordered my script so that the simple views were
refreshed first - now replication works fine.
thanks to all of you for your support
Thomas
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OH$2dhlHEHA.3840@.TK2MSFTNGP11.phx.gbl...
> Thomas,
> the order of running it is crucial - run sp_refreshview on the simple view
> then run it on the view that depends on it. This dependency should show up
> using sp_depends. Provided the dependency is recognised, then the order of
> application in the snapshot should be correct. I have run this
successfully
> before. However, if it doesn't work then Hilary's advice (postscripts) or
> sp_addscriptexec will do the same thing.
> Regards,
> Paul Ibison
>

Replication of Dynamic SQL Stored Procedures

I'm trying to set up replication on a Db that I just took over. When
replicating the SProcs the Distribution Agent stops with an error on the
Subscriber whenever it encounters an SP that is building a dynamic SQL
statement. I typically get the following error:
are not allowed. Add a name or single space as the alias name.
(Source: EVESTMENTDB6 (Data source); Error number: 1038)
There is nothing before the "are not allowed." Lookinig for error 1038 has
so far been fruitless. I have been rewriting the Dynamic SQl but I know that
at least some of the SProc's are really going to need it.
Jay Croft
Senior Systems Architect
Can you post one of the problem procs here?
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
"Jay Croft" <JayCroft@.discussions.microsoft.com> wrote in message
news:654660AB-4A31-4275-BD3E-A432D815B7E4@.microsoft.com...
> I'm trying to set up replication on a Db that I just took over. When
> replicating the SProcs the Distribution Agent stops with an error on the
> Subscriber whenever it encounters an SP that is building a dynamic SQL
> statement. I typically get the following error:
> are not allowed. Add a name or single space as the alias name.
> (Source: EVESTMENTDB6 (Data source); Error number: 1038)
> There is nothing before the "are not allowed." Lookinig for error 1038
> has
> so far been fruitless. I have been rewriting the Dynamic SQl but I know
> that
> at least some of the SProc's are really going to need it.
> --
> Jay Croft
> Senior Systems Architect
|||These SP's were written by an untrained junior programmer, but they've been
working in a production DB for two months.
/****** Object: StoredProcedure
[dbo].[eaSP_analytics_select_benchmark_perf_data] Script Date: 02/08/2006
18:14:39 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[eaSP_analytics_select_benchmark_perf_data]') AND
OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE procedure [dbo].[eaSP_analytics_select_benchmark_perf_data]
@.benchmark_type varchar(20),
@.view_name varchar(40),
@.risk_benchmark_id int,
@.earliest_date datetime,
@.latest_date datetime,
@.qorm char(1)
as
begin
SET NOCOUNT ON
declare @.strSQL varchar(1000)
if @.benchmark_type = ''Index''
Set @.strSQL = "SELECT benchmark_id, product_name, return_date, month,
year, value from " + @.view_name + " with (nolock) "
Set @.strSQL = @.strSQL + " WHERE benchmark_id = " + Cast(@.risk_benchmark_id
as varchar)
Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) + "'' "
Set @.strSQL = @.strSQL + " order by year desc, month desc"
Exec(@.strSQL)
if @.benchmark_type = ''DB Product''
Set @.strSQL = "SELECT firm_short, user_entered_firm_name,
product_category_code, vehicle_type, gross_or_net, base_currency, product_id
as benchmark_id, product_name, return_date, month, year, product_performance
as value from " + @.view_name + " with (nolock) "
Set @.strSQL = @.strSQL + " WHERE product_id = " + Cast(@.risk_benchmark_id
as varchar)
Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) + "'' "
Set @.strSQL = @.strSQL + " and quarterly_or_monthly = ''" + @.qorm + "'' and
product_performance is not null "
Set @.strSQL = @.strSQL + " order by year desc, month desc"
Exec(@.strSQL)
SET NOCOUNT OFF
end
'
END
GO
Jay Croft
Senior Systems Architect
"Hilary Cotter" wrote:

> Can you post one of the problem procs here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||Hi Jay,
Replication of text objects that use " instead of ' for delimiting string
literal is not supported (or simply broken) in SQL2000 although the SQL2005
snapshot agent will automatically convert these "s into 's. If there are not
many procedures that use " for delimiting string literals, you may want to
consider rewriting them to use ' instead. Otherwise you would need to use
the Database Import\Export wizard to transfer these stored procedures
instead.
HTH
-Raymond
"Jay Croft" <JayCroft@.discussions.microsoft.com> wrote in message
news:0D89C509-557E-4DA1-A99F-560BD91688C7@.microsoft.com...
> These SP's were written by an untrained junior programmer, but they've
> been
> working in a production DB for two months.
> /****** Object: StoredProcedure
> [dbo].[eaSP_analytics_select_benchmark_perf_data] Script Date:
> 02/08/2006
> 18:14:39 ******/
> SET ANSI_NULLS OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
> OBJECT_ID(N'[dbo].[eaSP_analytics_select_benchmark_perf_data]') AND
> OBJECTPROPERTY(id,N'IsProcedure') = 1)
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
>
>
> CREATE procedure
> [dbo].[eaSP_analytics_select_benchmark_perf_data]
> @.benchmark_type varchar(20),
> @.view_name varchar(40),
> @.risk_benchmark_id int,
> @.earliest_date datetime,
> @.latest_date datetime,
> @.qorm char(1)
> as
> begin
> SET NOCOUNT ON
> declare @.strSQL varchar(1000)
> if @.benchmark_type = ''Index''
> Set @.strSQL = "SELECT benchmark_id, product_name, return_date, month,
> year, value from " + @.view_name + " with (nolock) "
> Set @.strSQL = @.strSQL + " WHERE benchmark_id = " + Cast(@.risk_benchmark_id
> as varchar)
> Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
> varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) +
> "'' "
> Set @.strSQL = @.strSQL + " order by year desc, month desc"
> Exec(@.strSQL)
> if @.benchmark_type = ''DB Product''
> Set @.strSQL = "SELECT firm_short, user_entered_firm_name,
> product_category_code, vehicle_type, gross_or_net, base_currency,
> product_id
> as benchmark_id, product_name, return_date, month, year,
> product_performance
> as value from " + @.view_name + " with (nolock) "
> Set @.strSQL = @.strSQL + " WHERE product_id = " + Cast(@.risk_benchmark_id
> as varchar)
> Set @.strSQL = @.strSQL + " AND return_date >= ''" + Cast(@.earliest_date as
> varchar) + "'' AND return_date <= ''" + Cast(@.latest_date as varchar) +
> "'' "
> Set @.strSQL = @.strSQL + " and quarterly_or_monthly = ''" + @.qorm + "'' and
> product_performance is not null "
> Set @.strSQL = @.strSQL + " order by year desc, month desc"
> Exec(@.strSQL)
> SET NOCOUNT OFF
> end
> '
> END
> GO
> --
> Jay Croft
> Senior Systems Architect
>
> "Hilary Cotter" wrote:
>

replication monitoring

How do u check for replication latencies or if say the log reader or
distribution agent has stopped due to some failures ? Is there an easy way
to track this without buying some 3rd party tool. How can one write a query
? Love to hear some of ur ways of detecting delays in replications..
To check for failures use replication alerts.
It is extremely rare to see log reader agents not keeping up. If want to
check you can use sp_repltrans and fire an alert if rows are returned. You
can also query MSDistribution_status and raise an alert if you are getting
too far behind.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:umLppaVSFHA.3288@.TK2MSFTNGP14.phx.gbl...
> How do u check for replication latencies or if say the log reader or
> distribution agent has stopped due to some failures ? Is there an easy way
> to track this without buying some 3rd party tool. How can one write a
query
> ? Love to hear some of ur ways of detecting delays in replications..
>

Replication Monitor in VB6

Hello,
I need to continuously monitor the status of a SQL Server 2000 Merge
Replication agent; I was originally hoping to use the SQL Merge ActiveX
control, but that appears to only give me a status when I'm starting and
stopping merge replication from the same VB program. What I need to do is
stop and start replication from Enterprise Manager, but monitor for any
problems with VB. Any ideas? I've can work around the problem by watching
a table for certain records (I forget offhand the name of the table in
distribution) but I'd rather do something a bit more event driven as opposed
to a polling solution like this.
Any help would be greatly appreciated! Thanks!
MBC
Microsoft implements a polling method for this. Basically you would want to
poll the MSdistribution_history and msrepl_errors tables in the distribution
database.
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
"News" <mbcdev@.yahoo.com> wrote in message
news:AOGdnfu-7s9YeRzfRVn-pA@.wideopenwest.com...
> Hello,
> I need to continuously monitor the status of a SQL Server 2000 Merge
> Replication agent; I was originally hoping to use the SQL Merge ActiveX
> control, but that appears to only give me a status when I'm starting and
> stopping merge replication from the same VB program. What I need to do is
> stop and start replication from Enterprise Manager, but monitor for any
> problems with VB. Any ideas? I've can work around the problem by
watching
> a table for certain records (I forget offhand the name of the table in
> distribution) but I'd rather do something a bit more event driven as
opposed
> to a polling solution like this.
> Any help would be greatly appreciated! Thanks!
> MBC
>

Replication mechanism

Hello,
Can anybody here explain me shortly how the mechanism of replication
generally works? (with SQL Server CE Agent, SQL Server Agent, etc.)
thx
robert
Robert,
the 'SQL Server CE documentation' has a good set of descriptions and BOL
will explain standard replication. Is there something specific you have in
mind?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||With merger replication each row has a tracking column called a rowguid
column. Each row has a different value for this rowguid column, but
globally tables in the publisher and all subcribers which contain this
row will have the same value for this guid. This enables the merge
replication process to track changes to this row. This of it as a
Social Security Number, or Social Insurance Number (not sure what the
non American, non Canadian equivalents are).
Each table which is part of a merge publication has triggers of it, so
if one of the rows is modified tracking information is written to
tracking tables.
When the merge agent runs (run by SQL Server Agent) it consults these
tracking tables to determine what has changed since the last time it
has run. It has a counter called a generation which increments each
time the merge agent runs. It then gets a list of the rows which has
changed since the last time it ran and brings them to the publisher in
batches.
When the list of rows arrives at the publisher the publisher compares
its list with the list of changes it has and figures out what goes
where. If changes have to be made on the subsciber it fires procs there
to make these changes, if changes have to be made locally it uses sql
statements. If it detects that the same rows have changed on both the
subscriber and publisher it figures out who wins by using the conflict
resolution policy and logs the conflict info to conflict tracking
tables.
It then gets the next batch.
SQL CE does much the same thing - only it is done programmatically.
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

Replication Issue

When Trying to Replicate I got an issue with the Snapshot Agent.

Please be kind enough to help me I am developing a system using SQL server Mobile for a Construction Site. This is for my final Project in Bachelors Degree.

Please reffer the Error that I got. bellow.

Creating Publication

- Creating Publication 'SQLMobile' (Success)

· SQL Server created publication 'SQLMobile'.

- Adding article 3 of 3 (Success)

· Article 'FlightData' was added.

· Article 'MembershipData' was added.

· Article 'sysdiagrams' was added.

- Starting the Snapshot Agent (Warning)

Messages

SQL Server could not start the Snapshot Agent.(New Publication Wizard)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantStartSnapshotAgent&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQLServerAgent is not currently running so it cannot be notified of this action.
Changed database context to 'SQLMobile'. (Microsoft SQL Server, Error: 22022)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=22022&LinkId=20476

Thanks

Mark.

The error is:

- Starting the Snapshot Agent (Warning)

Messages

SQL Server could not start the Snapshot Agent. (New Publication Wizard)

You need to see why the snapshot agent could not be started. You can start by drilling down into the history of the snapshot agent in Replication Monitor, you can also query the distribution database for table MSsnapshot_history.

replication is in pending mode

hi guys,
I don't understand something, when I use domain account to do replication,
it's in pending mode for snapshot agent, but once I use sa account to do
replication, then it's in active mode. why can't I use domain account to
create replication?
The replication subsystem can't figure out if the domain account has rights
to start the service. A patch, not sure which one, broke this sometime ago.
I logon and register my servers in EM using the sa account and everything is
peachy.
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
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23S%23iz20yFHA.596@.TK2MSFTNGP12.phx.gbl...
> hi guys,
> I don't understand something, when I use domain account to do
> replication, it's in pending mode for snapshot agent, but once I use sa
> account to do replication, then it's in active mode. why can't I use
> domain account to create replication?
>

Replication initializing

Hi All,
Log reader agent shows as initializing. I tried changing read batchsize from
1- 100 with noluck. If I start, it sometimes unable to do sp_repl
commands.....
Can you help?
Regards,
Prashanth
May be the logreader agent has a lot to catch up on - that is, lot of
replicated transactions in the log. I suggest you let it continue, and catch
the exact error message, if it fails. You can look at the error message by
right clicking on the agent and selecting 'Error Details'.
Optionally, you can increase the timeout values for the agent. See
"Replication Log Reader Agent Utility" in SQL Server 2000 Books Online.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Prashanth" <prashanthlv@.talisma.com> wrote in message
news:%23JuZor1DFHA.3376@.TK2MSFTNGP12.phx.gbl...
Hi All,
Log reader agent shows as initializing. I tried changing read batchsize from
1- 100 with noluck. If I start, it sometimes unable to do sp_repl
commands.....
Can you help?
Regards,
Prashanth