October 29, 2009 at 11:27 am
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?
October 29, 2009 at 12:10 pm
What do you get when you run select * from syssubscriptions in the publication database? Does the subscription show up there?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 29, 2009 at 12:18 pm
David Benoit (10/29/2009)
What do you get when you run select * from syssubscriptions in the publication database? Does the subscription show up there?
No it doesn't. syssubscriptions is completely empty.
October 29, 2009 at 12:25 pm
Well, since this isn't working now anyway, I would try to clear that data from the distribution database and try adding it again. Sounds like things aren't getting cleaned up properly.
Did you say when you add it with SSMS it actually adds it appropriately? If so, when you drop the subscription with SSMS does it clear the record from the distribution database?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 29, 2009 at 1:07 pm
I tried completely disabling replication, which in process drops the distribution database, and resetting everything up. I am still getting the same problematic behavior.
October 29, 2009 at 1:11 pm
More wondering if when you were to create the publication, subscription with SSMS gui and then drop them if it would actually remove that entry from MSSubscriptions in the distribution database.
Again, can you create the subscription successfully using the GUI? Please confirm.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 29, 2009 at 1:18 pm
Publication works regardless of whether I use the GUI or the script generated via the gui to create the publication.
However, Subscription *only* works when created with the GUI. Subscription doesn't work when using the script generated by the gui.
October 29, 2009 at 1:23 pm
So, if you create the subscription with the GUI and then drop the subscription does it remove the entry from MSSubscriptions on the distribution database? If it does I would try to script to create the subscription again and see what happens.
Basically, I'm thinking you are running into some metadata issues but obviously can't prove that. If the entry in MSSubscriptions is removed following a the drop of the subscription via the GUI I would be inclined to think that the script to add the subscription would then work.
Hopefully this makes sense. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 29, 2009 at 1:46 pm
Replication
+ Local Publications
+ [PublisherDatabase]: PublicationName
+ [SERVER\INSTANCE].[SubscriptionDatabase]
+ Local Subscriptions
Creating the subscription with the GUI gives both the entry "+ [SERVER\INSTANCE].[SubscriptionDatabase]" and an entry under "Local Subscriptions".
Deleting the entry under "Local Subscriptions" with the GUI has no effect on the contents of MSSubscriptions. Deleting the entry "+ [SERVER\INSTANCE].[SubscriptionDatabase]" with the GUI removes the entry with subscriber_db column = "SubscriberDatabase" in MSSubscriptions, but not the entries with subscriber_db column = "virtual"
Does that help clarify the problem any? Thanks for your help.
October 29, 2009 at 2:49 pm
So you are dropping the subscription at the subscriber instead of at the publisher? Any reason why? Are you running the script to create the subscription at the publisher? If not, could you try?
I haven't tried to provide help with replication in the past on forums as my knowledge has been minimal but as it has increased I thought I was ready. I am finding though that replication is one of those things that is a whole lot easier when you can be hands on. 🙂
Thanks for being patient!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 30, 2009 at 9:41 am
Right now the publisher and the subscriber are on the same machine.
November 4, 2009 at 1:04 pm
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
November 4, 2009 at 1:59 pm
Glad you figured it out but I'm surprised that not starting the snapshot agent caused your subscription not to be created as they are not really connected. I wonder if it has something to do with the publication properties?.?.
Regardless, glad you have it working.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 14, 2010 at 2:16 pm
Our whole database team struggles with this problem. We all ended up having to add the subscriptions with the GUI. I figured out how to get a script to work, but it requires manual intervention as well.
The problem seems to be you have to run the subscription add script on the publisher and then on the Subscriber within seconds of each other. Running the entire script on the Publisher, then running the second script against the subscriber more than a few seconds of each other, results in what you're seeing. Drives us nuts.
If anyone can help us figure out how to get this to work, say... like in a job... I'd really appreciate the help.
Your solution doesn't work for us. Looks like you are using Push subscriptions, we are using Pull, and starting the snapshot hasn't been our issue here.
Lezza
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply