Post reply

looking for articles

  • June 23, 2004 at 10:32 am

    #163904

    Hi

    I have two subscription for one publisher ,In one subscription i am publishing all tables for one server and for second i am publishing some articles for diff server,

    Now i want to look those some articles from publisher properties but it does not show the some articles and always show the all articles of first one .Kindly suggest how can i find my some articles for second subscriber.

     

    Thanks

  • June 23, 2004 at 5:29 pm

    #511681

    Articles belong to a Publication, Did you create 2 Publications, 1 for each Subscriber? If each Subscriber is suppose to have different Articles, then you need to creat 2 seperate Publications!

    Kindest Regards,

  • June 24, 2004 at 12:14 am

    #511703

    No my publisher is one only for both.....

  • June 24, 2004 at 12:20 am

    #511707

    If you only have 1 Publication, then how can you replicate different Articles to 2 Subscribers?

    Kindest Regards,

  • June 24, 2004 at 3:41 am

    #511732

    publisher is same for both but it is publishing some articles for one and all articles for other.

    Thanks

  • June 24, 2004 at 4:46 am

    #511738

    try this - sp_helpsubscription @Publication = 'PubName'

    Exec this in the published db and this will show you articles published and who is subscribered to them.

    Obviously you can have one pub serving different subs - especially if you use scripts to manage your replication instead of EM.

    Regards.

     

     

  • June 24, 2004 at 10:15 am

    #511811

    Thanks but i need the list of articles for both the subscription separately.

    Thanks

  • June 25, 2004 at 4:18 pm

    #512052

    Easy! Simply add the @subscriber parameter.

    Suggest you look sp_helpsubscription syntax in BOL -

    sp_helpsubscription [ [ @publication = ] 'publication' ]

        [ , [ @article = ] 'article' ]

        [ , [ @subscriber = ] 'subscriber' ]

        [ , [ @destination_db = ] 'destination_db' ]

        [ , [ @found =] found OUTPUT ]

    So we have a publication called 'MyPub', and two subscriptions - 'MyFirstSub' and 'MySecondSub'

    As the defaults for @article and @subscriber are '%' (to get all) you could simply run sp_helpsubscription with different @subscriber parameter values.

    eg - List articles from MyPub for MyFirstSub

    exec sp_helpsubscription @publication = 'MyPub', @subscriber = 'MyFirstSub'

    eg - List articles from MyPub for MySecondSub

    exec sp_helpsubscription @publication = 'MyPub', @subscriber = 'MySecondSub'

    Its easy to write some T-SQL code to loop your pubs and subs, check the articles and output lists or found flags to find what you are asking for.

    HTH

     

    Stu

     

  • July 5, 2004 at 9:21 am

    #513331

    But this give me only:

    command executed successfully:

  • July 5, 2004 at 5:15 pm

    #513362

    That's what you get when you exec the stored proc in the wrong database context - make sure you exec sp_helpsubscription in the context of the published database.

    Ok here's what I've got so we're on the same page:

    SQL Server 2000, SvcPk 3 (for both the subs and pub)

    A single publication with all tables from a user database (no filtering on any articles - ie all columns and all rows published - nice and simple.)

    2 push subscribers

    I connect to my published database (ie use MyDb).

    When I exec:

    sp_helpsubscription @publication = 'MyDb', @subscriber = 'MySubA'

    I get a list of subscribed articles for this sub - in this case 1 article.

    When I exec:

    sp_helpsubscription @publication = 'MyDb', @subscriber = 'MySubB'

    I get a list of subscribed articles for this sub - in this case 2 articles.

     

    Now as much as everyone hates people looking at system tables if you look at syssubscriptions in your published database you'll similar info in raw form.

    Eg

    Use MyDb

    select  artid

     ,srvid

     ,dest_db

     ,status

     ,sync_type

     ,login_name

     ,subscription_type

     ,distribution_jobid

     ,timestamp

     ,update_mode

     ,loopback_detection

     ,queued_reinit  from dbo.syssubscriptions

     

    Check out Books-On-Line it explains it better than I can.

    Cheers,

    Stu 

Viewing 10 posts - 1 through 9 (of 9 total)

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