Sql Server 2005 Replication: How to setup subscription with a sql script?

Question

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

Answer 1

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:

EXEC sp_startpublication_snapshot @publication = N'InstitutionPublication'

So my full script for the subscriber after modification is:

-----------------BEGIN: Script to be run at Publisher 'SERVER\INSTANCE'-----------------
-----------------BEGIN: Script to be run at Publisher 'SERVER\INSTANCE'-----------------
use [Profile]
exec sp_addsubscription @publication = N'PublicationName', @subscriber = N'server\instance', @destination_db = N'destinationDatabaseName', @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'destinationDatabaseName', @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 = 20091104, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'SERVER\INSTANCE'-----------------

EXEC sp_startpublication_snapshot @publication = N'PublicationName';
GO