Articles
-
Hi to all:
This might be a simply question but due to some testing that i'm doing I found something I never tried before and don't know if it possible to do. Suppose that I have a publication With 10 articles and 9 subscribers. One of the articles is for all the subscribers, and then each subscriber has his own article to subscriber to. Is it possible that a subscriber subscribe to only to articles of a publication?.
I have always used @article = 'all' or only one article.
Thanks in advanced
-
Yes, but you have to itterate through the articles you want the subscriptions to.
Use:
sp_addsubscription @publication = 'publication'
, @article = 'article'
, @subscriber = 'subscriber'
, @destination_db = 'destination_db'
, @sync_type = 'sync_type'
, @status = 'status'
, @subscription_type = 'subscription_type'
, @update_mode = 'update_mode'
, @loopback_detection = 'loopback_detection'
, @frequency_type = frequency_type
, @frequency_interval = frequency_interval
, @frequency_relative_interval = frequency_relative_interval
, @frequency_recurrence_factor = frequency_recurrence_factor
, @frequency_subday = frequency_subday
, @frequency_subday_interval = frequency_subday_interval
, @active_start_time_of_day = active_start_time_of_day
, @active_end_time_of_day = active_end_time_of_day
, @active_start_date = active_start_date
, @active_end_date = active_end_date
, @optional_command_line = 'optional_command_line'
, @reserved = 'reserved'
, @enabled_for_syncmgr = 'enabled_for_syncmgr'
, @offloadagent = remote_agent_activation
, @offloadserver = 'remote_agent_server_name'
, @dts_package_name = 'dts_package_name'
, @dts_package_password = 'dts_package_password'
, @dts_package_location = 'dts_package_location'
, @distribution_job_name = 'distribution_job_name'
to create the subscription and add the articles one at a time to the subscription. And of course, simply comment out the options you wouldn't want to use or that do not apply.
I use a table to hold my subscription info, and walk the table to add the articles. This also centralizes all your subscription info, and allows it to be build dynamically, with the right process behind it.
Just as a FYI, be sure to add all the articles you want before allowing the sync, or you will re-sync for each one.....
-
Interesting. Not sure if I like the idea yet or not!
Andy
-
Yes but in the article parameter, it only accept the word 'all' or the name of the article to publish. Do I have to execute sp_Addsubscription sp as many times as articles I have, and always changing the value in @article?
Yes! the idea to create a table with the articles and let the sp to build dinamically is new. Do you also have the parameters you need in that table?
-
Yep, once for each article.
The idea of metadata has been around for many many years. I just apply it to this the same as anything else, and I really can't even claim the idea as my own, just something I do. In some cases I store the parameters I need, and in some I don't. Really just depends on the replication I'm doing, and whether or not I believe that hard coding them will cause me to have to rewrite my procs that perform the builds of replication.
Generally, I only apply the metadata solution when building many publications, where I believe the articles will be changing. Other than that, it's easier to just build a script and apply it as needed. And of course, I guess it could come down to just whether I want to be modifying scripts, testing them, etc. or changing table data for a proven script...I'm sure you know what I mean. -
Well, once I finish re designing my repliaction topology, I think I also could use some tables and maybe build some stored procedures to automatically creates the script I need in replication.
Generally, I only apply the metadata solution when building many publications, where I believe the articles will be changing. Other than that, it's easier to just build a script and apply it as needed. And of course, I guess it could come down to just whether I want to be modifying scripts, testing them, etc. or changing table data for a proven script...I'm sure you know what I mean.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply