November 11, 2008 at 10:50 am
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...
November 13, 2008 at 5:11 am
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
[font="Verdana"]Markus Bohse[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply