2

In Sql Server 2014, how to query a list of Subscriptions & its articles for a given Publication from Publisher? (transactional replication)

Yes, there are easier ways to query from Distributor but I need a query from Publisher side.

Remote Distributor model (if it helps)

Thanks,

1

To get subscription list from a publisher database, you can run below tsql :

use publisherDB;
select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 

Incase if someone needs for merge replication :

use publisherDB
go
select distinct 
    sa.name as articlename,
    sp.publisher as publisherserver,
    sp.publisher_db as publisherdb,
    sp.name as publicationname
    ,ss.subscriber_server as subscriberservername 
from dbo.sysmergearticles sa
join dbo.sysmergepublications sp on sa.pubid = sp.pubid
join dbo.sysmergesubscriptions ss on ss.pubid = sa.pubid
order by subscriberservername
0

Found this sys proc, not sure if it is correct.

sp_helpsubscription

Any ideas for better solution?

Thanks,

  • EXEC sp_helpsubscription @publication = 'Publication', @article = 'Table', @found = @DoesSubscriptionAlreadyExist OUTPUT SELECT @DoesSubscriptionAlreadyExist – ToC Nov 14 '18 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.