Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

snapshot replication job starts immediately when created Expand / Collapse
Posted Tuesday, November 11, 2008 10:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 10, 2016 1:59 PM
Points: 5, Visits: 406
I have a snapshot replication job that copies most of the tables in a production database (on server SQL1) over to a reporting server (server SQL2) once per night. Both servers are running SQL 2000. This is a push subscription running from distributor and publisher SQL1 to subscriber SQL2.

When it is necessary to remove the subscription (for instance, when recently migrating SQL2 to new hardware) I run the following script and it seems to work fine.

exec sp_dropsubscription @publication = N'SQL1.OfficeDB', @article = N'all', @subscriber = N'SQL2', @destination_db = N'OfficeDB'

However, when I run the following script to add the subscription back, it results in problems.

exec sp_addsubscription @publication = N'SQL1.OfficeDB', @article = N'all', @subscriber = N'SQL2',
@destination_db = N'OfficeDB', @sync_type = N'automatic', @update_mode = N'read only', @offloadagent = 0,
@dts_package_location = N'distributor'

The system procedure adds a replication distribution job into the scheduler that starts running immediately.

The replication distribution job that is created has three steps:

1 - Distribution Agent startup message
2 - Run agent
3 - Detect nonlogged agent shutdown

The job detail on the second step, as programatically created by the procedure, is:

-Subscriber [SQL2] -SubscriberDB [OfficeDB] -Publisher [SQL1] -Distributor [SQL1] -DistributorSecurityMode 1 -PublisherDB [OfficeDB] -Continuous

I have learned to cancel the job as soon as running the 'sp_addsubscription' script. I'm certain that there are appropriate parameters to use with the 'sp_addsubscription' script that will schedule the jobs as desired but I'm apparently not smart enough to decipher them from BOL (not quite sure which parameters to make use of).

I have further discovered that if I remove the '-Continuous' from the script and then manually schedule the job appropriately (once per night at 2:15am), it works great.

Any recommendations on the sp_addsubscription parameters? Thank you in advance...
Post #600823
Posted Thursday, November 13, 2008 5:11 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, November 24, 2016 4:32 AM
Points: 4,429, Visits: 4,207
If you lookup 'sp_addsubscription' in books online, you will see that there are the parameters@frequency_type, frequency_interval, @active_start_date and @active_start_time_of_day.
These will create a schedule when to start replicating and how often.

Markus Bohse
Post #601995
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse