When going through the replication setup wizard, I can choose Generate script at the end. The script works fine for the Publication setup, however, when I run the generated script for Subscription setup, I run into issues and the replication doesn't work. If however, I don't generate the script, but simply have the wizard start the replication itself, everything works. I would really like to automate the subscription setup with a script.
The generated subscription script is:
-----------------BEGIN: Script to be run at Publisher 'SERVER\INSTANCE'-----------------
use [PublisherDatabase]
exec sp_addsubscription @publication = N'PublicationName', @subscriber = N'server\instance', @destination_db = N'SubscriberDatabase', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PublicationName', @subscriber = N'server\instance', @subscriber_db = N'SubscriberDatabase', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20091028, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'FLANDERS\TESTING'-----------------
When I run the above script, it completes without errors. However, the subscription never shows up in the Sql Server Management Studio Replication->Local Subscriptions tree, and the data is never replicated.
Oddly however, running the query:
use distribution
select * from MSSubscriptions
gives the result set:
publisher_database_id publisher_id publisher_db publication_id article_id subscriber_id subscriber_db subscription_type sync_type status subscription_seqno snapshot_seqno_flag independent_agent subscription_time loopback_detection agent_id update_mode publisher_seqno ss_cplt_seqno
--------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- -------------- ----------- ------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------- --------- ------ ---------------------------------- ------------------- ----------------- ----------------------- ------------------ ----------- ----------- ---------------------------------- ----------------------------------
3 0 PublisherDatabase 3 1 -1 virtual 0 1 1 0x00000027000001230003 0 1 2009-10-29 10:41:37.540 1 10 0 0x00000027000001230003 0x00000027000001230003
3 0 PublisherDatabase 3 1 -2 virtual 0 1 2 0x00000027000001230003 0 1 2009-10-29 10:41:37.603 1 11 0 0x00000027000001230009 0x00000027000001230009
3 0 PublisherDatabase 3 1 0 SubscriberDatabase 0 1 1 0x000000270000013B0008 0 1 2009-10-29 10:54:58.140 1 12 0 0x000000270000013B0008 0x000000270000013B0008
Which would seem to indicate the the subscription is present.
In case it matters, this is a push only publication/subscription, and both the publisher and subscriber databases are on the same server.
What else do I need to do to get subscriptions working from a sql script?
edit: cross-posted to http://www.sqlservercentral.com/Forums/Topic811043-291-1.aspx
Best Answer
Figured it out. When generating the subscription directly from management studio, it automatically starts the snapshot agent. However, the script that management studio generates for the subscription does not start the snapshot agent. Adding the following line to the generated script makes things work:
So my full script for the subscriber after modification is: