Replication to multiple subscribers question

We have an application that runs with MS SQL 2000 (SP3a) which is used for
tracking information from multiple sources. We are shortly going to be
setting up replication of this database with a secondary location, with a
third location planned by mid year. We know that there will be other
locations added over the course of the next few years. Our concern is that
if we have the snapshot expire, we will have to regenerate the snapshot and
reapply it to ALL subscribers before we can add a new subscriber. This is a
problem for 2 reasons. First because the foreign key relationships we have
between many of the tables appears to require that we delete the database on
the subscriber before we can apply the snapshot, and second because the
nature of our subscribers will mean that most of them will be disconnected
for long periods and as the number of subscribers grows it will be
increasingly unlikely that we will have them all connected at the same time.
It appears that our other option is to not have the snapshot expire, but MS
warns that this may cause performance problems but I haven't seen anything
that quantifies the potential performance problems.
Can anyone tell me what the "right" way of doing this is? What sort of
performance problems are we looking at if we don't have the snapshot expire?
Our database consists of approximately 100 or so user tables and currently
is about 300MB of data.
TIA
Ron L.
Ron,
I suspect the performance problems being talked about are proportional to
the amount of changes taking place to the data once the snapshot has been
made. EG if your snapshot contained static lookup tables, then having a
long-lived snapshot file wouldn't really be an issue. However if your
snapshot was of a productlisting table containing 1 million rows, and
product prices were changed on a regular basis (my case) then after
application of the snapshot you might find your merge agent (or distributor)
would struggle to send down the accumulated changes. BTW, in this case the
problem would be least for merge assuming records could be repeatedly
changed, and it avoids the issue of not being able to run the distribution
cleanup agent that you'll have.
HTH,
Paul Ibison
|||Paul
Thanks for the response. My data tends to be less changes and more
additions - we have logging records for text logs and records tracking
movement of parts within systems. At the moment, our largest table has just
over 100,000 records and the next largest is in the 20,000 record range.
One other option we were looking at was to have seperate publications
for each (or a small group of) additional server, thus making the snapshot
regeneration have less impact. Do you have any feeling as to whether this
is a better approach?
Thanks,
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23FVkMhWLEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Ron,
> I suspect the performance problems being talked about are proportional to
> the amount of changes taking place to the data once the snapshot has been
> made. EG if your snapshot contained static lookup tables, then having a
> long-lived snapshot file wouldn't really be an issue. However if your
> snapshot was of a productlisting table containing 1 million rows, and
> product prices were changed on a regular basis (my case) then after
> application of the snapshot you might find your merge agent (or
distributor)
> would struggle to send down the accumulated changes. BTW, in this case the
> problem would be least for merge assuming records could be repeatedly
> changed, and it avoids the issue of not being able to run the distribution
> cleanup agent that you'll have.
> HTH,
> Paul Ibison
>
|||Ron,
I agree that this approach leads to more granularity and therefore is more
versatile for your needs, As long as the publications are distinct units
then it should be OK - eg if there are PK-FK relationships across
publications then it's a no-go, so it depends how related the tables are.
Regards,
Paul
|||Paul
What we are thinking is multiple publications covering the same set of
tables. Is this not going to work?
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e454$eeLEHA.1484@.tk2msftngp13.phx.gbl...
> Ron,
> I agree that this approach leads to more granularity and therefore is more
> versatile for your needs, As long as the publications are distinct units
> then it should be OK - eg if there are PK-FK relationships across
> publications then it's a no-go, so it depends how related the tables are.
> Regards,
> Paul
>
|||Ron,
I've not heard of this arrangement before, but it sounds feasible with merge
replication.
I guess the only downside is increased maintenance.
Cheers,
Paul

Replication service error

hello everybody
when i create the subscribers . i got error . i try both account system
account and this account ...
but same error
Subscriptions were created successfully at the following Subscribers:
BOYD
SQL Server Enterprise Manager could not start the service 'SQLSERVERAGENT'
on server 'SERVER1'.
5 - (Access is denied)
Sam,
on Server1 check the domain login used by the sqlserveragent service. Make
sure it is a local administrator and that the password is correct, then
start the service manually using control panel.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I got the same error this morning. Could you please give me more detail on
how to check the login account.
Thanks.
Perayu
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uBf5LtLyFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Sam,
> on Server1 check the domain login used by the sqlserveragent service. Make
> sure it is a local administrator and that the password is correct, then
> start the service manually using control panel.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||OK - on server1 open up control panel, administrative tools, services. Find
the sqlserveragent service (assuming the default instance), open the
properties and make sure it uses a domain user that is a local
administrator. If you are unclear about which account has these privileges,
it's probably best to get your network admin to create an account for you.
Once selected, restart the service.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you so much!
It is working now.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OCMwK8PyFHA.2516@.TK2MSFTNGP12.phx.gbl...
> OK - on server1 open up control panel, administrative tools, services.
> Find the sqlserveragent service (assuming the default instance), open the
> properties and make sure it uses a domain user that is a local
> administrator. If you are unclear about which account has these
> privileges, it's probably best to get your network admin to create an
> account for you. Once selected, restart the service.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Hello Mr Paul
still i got same problem , if have any privalage for domain account
thanks
Sam
"Perayu" wrote:

> I got the same error this morning. Could you please give me more detail on
> how to check the login account.
> Thanks.
> Perayu
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:uBf5LtLyFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
>
|||Sam,
does this mean that you can't start the sql server agent at all? If so, make
sure that the account you're using for the sql server agent is added to the
sysadmin role. it shoyuld be in this role if your builtin\administrators are
there and the domain account is a local admin one, but many DBAs disable
this as it is seen as a security backdoor.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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 OS Errror 5

I'm using SQLDistribution.SQLDistribution.2 Active X
within a PowerBuilder App. The pull subscribers intitiate
replication from the application. Both publishers and
subscribers are MSDE SQL 2000.
I get: The process could not read file 'bla
bla'\snapshot.pre' due to OS error 5 - UNLESS the user
login running the application has been specifically
granted full control on the snapshot share folder. The
snapshot folder is a public share, not an administrative
share.
It is not a realistic solution for me to grant every user
full control over the share folder. I thought by
using "Impersonate SQL Server Agent Account" for the
subscribcriptions, that the subscribing servers startup
login account would be used to access the snapshot share
folder but apparently not?
Is there someway to use the replication activex but avoid
having to grant every application user full control of the
snapshot folder?
Thanks,
Pat
the minimum security on the files and folders is read and list folder and
contents. The minimum share permission required is read. Is the allow
inheritable permissions from parent to propagate to this object checked?
Are you sure that the account you are using has both of these rights on the
snapshot share and underlying files and folders?
You are correct when you select the impersonate option on a pull
subscription the SQL Server agent account on the Subscriber must have rights
to access the snapshot folder.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Pat" <anonymous@.discussions.microsoft.com> wrote in message
news:47f001c4734c$19e2cfe0$a501280a@.phx.gbl...
> I'm using SQLDistribution.SQLDistribution.2 Active X
> within a PowerBuilder App. The pull subscribers intitiate
> replication from the application. Both publishers and
> subscribers are MSDE SQL 2000.
> I get: The process could not read file 'bla
> bla'\snapshot.pre' due to OS error 5 - UNLESS the user
> login running the application has been specifically
> granted full control on the snapshot share folder. The
> snapshot folder is a public share, not an administrative
> share.
> It is not a realistic solution for me to grant every user
> full control over the share folder. I thought by
> using "Impersonate SQL Server Agent Account" for the
> subscribcriptions, that the subscribing servers startup
> login account would be used to access the snapshot share
> folder but apparently not?
> Is there someway to use the replication activex but avoid
> having to grant every application user full control of the
> snapshot folder?
> Thanks,
> Pat

Replication of users

Hi,
I have setup a process so I can replicate users and logins between my
publishers and subscribers. This I have done using the replication of stored
procedure execution.
Now the problem I have is to replicate the sp_revokedbaccess sp from
publisher to subscriber.
IT throws me error when I try to replicate the execution of sp saying that
this sp can not be part of a transaction.
Is there any other way to replicate this functionality.
Please advice..
Thank you
Sam
use sp_addscriptexec to replicate the commands to all subscribers deployed
via a unc.
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
"sam" <sam@.discussions.microsoft.com> wrote in message
news:576BFBFB-8C8C-4DCE-8510-BFC472F2C8F9@.microsoft.com...
> Hi,
> I have setup a process so I can replicate users and logins between my
> publishers and subscribers. This I have done using the replication of
stored
> procedure execution.
> Now the problem I have is to replicate the sp_revokedbaccess sp from
> publisher to subscriber.
> IT throws me error when I try to replicate the execution of sp saying that
> this sp can not be part of a transaction.
> Is there any other way to replicate this functionality.
> Please advice..
> Thank you
> Sam
|||Hi Hilary,
Thank you for your response.
I 'm still little confused about how to create a script on fly when a db
user is removed via application and so I want to replicate the same function
on subscriber.
I have to create a script on fly from the sql query execution at the
publisher when a user fires a sp_revokedbaccess command. And we have
xp_cmdshell is blocked on server for security reasons.
What do you think about this situation and how should I implement the
replication functionality?
Sam
"Hilary Cotter" wrote:

> use sp_addscriptexec to replicate the commands to all subscribers deployed
> via a unc.
> --
> 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
> "sam" <sam@.discussions.microsoft.com> wrote in message
> news:576BFBFB-8C8C-4DCE-8510-BFC472F2C8F9@.microsoft.com...
> stored
>
>
|||I'm sorry I either misunderstood your question or misread your post.
There is no way to dynamically sense the changing users or logins and
replicate this, other than by doing log shipping.
You have to know in advance what user has been added, dropped, modified, and
then encapsulate the commands to carry out this modification and use
sp_addscriptexec to replicate this command to all subscribers.
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
"sam" <sam@.discussions.microsoft.com> wrote in message
news:D1F7D6BA-6C2D-4ECE-8B05-BA1053EAB6E4@.microsoft.com...
> Hi Hilary,
> Thank you for your response.
> I 'm still little confused about how to create a script on fly when a db
> user is removed via application and so I want to replicate the same
function[vbcol=seagreen]
> on subscriber.
> I have to create a script on fly from the sql query execution at the
> publisher when a user fires a sp_revokedbaccess command. And we have
> xp_cmdshell is blocked on server for security reasons.
> What do you think about this situation and how should I implement the
> replication functionality?
> Sam
> "Hilary Cotter" wrote:
deployed[vbcol=seagreen]
that[vbcol=seagreen]

Replication Limiting Subscribers

How can we limit the number of anonymous merge subscribtions to a
given publication? Can we limit it by machine MAC address?
(Bump!)
Guys I really need an answer to this question.
How can we limit the number of anonymous merge subscribtions to a
given publication (assuming i can have only one sql account per
publication and no NT authentication)?
The problem with giving a set of sql accounts is that they can be
reused.
Can we limit it by machine MAC address?
Thanks,
Hananiel
hananiel@.yahoo.com (Hananiel) wrote in message news:<e338e321.0410130607.37e214d5@.posting.google. com>...
> How can we limit the number of anonymous merge subscribtions to a
> given publication? Can we limit it by machine MAC address?

Replication Issue

We have replication set up from master sql server to 4 other sql
servers. Replication is transactional and 4 other sql servers are
subscribers and master database is publisher. The issue we are facing
is on subscriber database , the tables sometimes gets blocked and the
query times out. But if we query it again or refresh the page(that
sends the query again) it works fine.
Can it be because of some configuration issues with replication that
it holds the table until it replicates data into it.Rahul,
The problem may be that your query is acquiring shared locks on the same
data that replication needs to update. For querying subscriber data, you can
use the NOLOCK or READPAST hints in your queries. Don't attempt to update
any replicated data directly; use updating subscribers if you want to do
that.
Ron
--
Ron Talmage
SQL Server MVP
"Rahul" <rahulkhushoo@.yahoo.com> wrote in message
news:246c8d90.0311071425.204116e6@.posting.google.com...
> We have replication set up from master sql server to 4 other sql
> servers. Replication is transactional and 4 other sql servers are
> subscribers and master database is publisher. The issue we are facing
> is on subscriber database , the tables sometimes gets blocked and the
> query times out. But if we query it again or refresh the page(that
> sends the query again) it works fine.
> Can it be because of some configuration issues with replication that
> it holds the table until it replicates data into it.

Replication Identity Ranges

Hello,
We have set up Merge replication, using sql 2000. We have one publisher and
four subscribers. When setting up the articles on the publisher, we manually
set an identity range for one of the tables. The range size at the publisher
and the subscriber is set to 5000, and the assign new range is set to 95%.
When looking at the design of the table and ckecking the replication
constraint, on the first subscriber, the range is 103000 - 108000. The next
subscriber has a range of 105000 - 111000. All the subscribers seem to have
a range that overlap with each other. The table has an identity column and
it is set to Not for Replication.
It is my understanding that each subscriber should have a range that do not
overlap. I have checked other tables on the subscribers and they each have
ranges that do not overlap.
I hope I have given enough information, and I hope someone could shed some
light on this.
Thanks in advance
Identity ranges should never overlap.
Did you use the same snapshot for each subscriber?
|||Hello,
we used the same snapshot for each subscriber. This problem has now been
corrected. We manually modified the MSrepl_identity_range table on each
subscriber, then forced replication. This fixed the problem.
"Jim Breffni" wrote:

> Identity ranges should never overlap.
> Did you use the same snapshot for each subscriber?
>