Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


snapshot replication job starts immediately when created


snapshot replication job starts immediately when created

Author
Message
Kerry Conner-320974
Kerry Conner-320974
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 408
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...
MarkusB
MarkusB
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4449 Visits: 4208
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.
http://msdn.microsoft.com/en-us/library/aa239405(SQL.80).aspx

Markus Bohse
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search