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...