sp_addsubscription ..... @article = N''all''

  • Simon Liddle

    SSCrazy

    Points: 2573

    Hi,

    (SQL2k pub, SQL2k dist, SQL7 subs)

    I used Enterprise Manager's Generate Script option on one of my publications and noticed that when creating the subscriptions, for most of the subscribers, the script generates:

    sp_addsubscription .... @article = N'all' ....

    However, for one of the subscribers, there are a number of statements, each specifying one of the articles in the publication but ultimately making a subscription to the same articles as the other subscribers, i.e. all of them:

    sp_addsubscription .... @article = N'supplier' .....

    sp_addsubscription .... @article = N'customer' .....

    sp_addsubscription .... @article = N'user' .....

    Is there any difference between these two ways of subscribing to all articles? Is one way 'better' than the other?

    Thanks!

    Simon

  • Erik Bitemo

    SSCrazy

    Points: 2062

    Hi Simon,

    There's no difference until you add more articles to your publications. So you can decide which one is the better for you. I would choose the 'all' instead of the lists, because easier to read and understand, and if there's any change in the publication, you don't need to recreate the scripts. And imagine what if you drop a table...

    I hope it was an answer to your question.

  • abhijit.dey

    Old Hand

    Points: 314

    its depends upon what option you are using for @sync_type if you are using repl support only option then 1st option is best. because There is a bug in sql server if you mention 'all' while using @sync_type repl support only, in future if you change any schema then that will not snapshot properly.

  • Dwaine Wright

    SSC Journeyman

    Points: 95

    Simon Liddle - Friday, August 31, 2007 4:32 AM

    Hi,(SQL2k pub, SQL2k dist, SQL7 subs)I used Enterprise Manager's Generate Script option on one of my publications and noticed that when creating the subscriptions, for most of the subscribers, the script generates: sp_addsubscription .... @article = N'all' ....However, for one of the subscribers, there are a number of statements, each specifying one of the articles in the publication but ultimately making a subscription to the same articles as the other subscribers, i.e. all of them: sp_addsubscription .... @article = N'supplier' ..... sp_addsubscription .... @article = N'customer' ..... sp_addsubscription .... @article = N'user' .....Is there any difference between these two ways of subscribing to all articles? Is one way 'better' than the other? Thanks!Simon

    I know this is an old post, but the issue still remains.

    When you script out and see each article added separately, that means that that subscriber was created without the @articles = "all" option and/or that an individual article was removed from the sub after creation.  If you usually subscribe to all articles, this is actually a good way to detect errors!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply