[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 677
  • Last Modified:

Retrieve a list of Replication Agents (merge/pull subscribers)

Hi All,

Does anyone know how to programmatically (T-SQL) get a list of replication agents.  The list of agents is obviously shown under Replication Monitor / Publishers / Publisher and the Publication in Enterprise Manager.  However, I need to write a .NET App that monitors the status of replication and perform numerous custom maintenance options for an admin user.

Is there an internal SP that can retrieve this information?  The agents are Merge/Pull Subcribers.

For clarification of the list I'm looking for, see the GIF image link below:

http://www.limiteds.com/pms/subscriberagentlist.gif

Many thanks,

Treadmill
0
treadmill
Asked:
treadmill
  • 3
  • 2
1 Solution
 
imran_fastCommented:
you have to look into the following tables which will be specific to the database.
syspublications
syssubscribtions
sysmergepublications

0
 
treadmillAuthor Commented:
Within my publication database, I do not have the tables syspublications or syssubscriptions.  sysmergepublications I do have, but there is only 1 row in that table.

The subscribers are anonymous pull subscribers to the publication database, which is a Filtered Merge Publication.

I also should have said that I'm referring to an SQL Server 2000 installation, just in case it differs from 2005.

Any ideas where I can get this information from?

0
 
treadmillAuthor Commented:
I finally found the answer myself by running a trace on the publication and found that sp_MSenum_merge_subscriptions, which is located in the "distribution" database will retrieve the exact results I require.

Here's an example on how to execute this:

exec [distribution].dbo.sp_MSenum_merge_subscriptions @publisher = N'ACCDATA1', @publisher_db = N'FIVESTARSYSTEMS', @publication = N'5StarFilt', @exclude_anonymous = 0

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
treadmillAuthor Commented:
Also, you can find them in this database table:

select * from distribution..MSmerge_agents
0
 
imran_fastCommented:
Hi,
Tread mill close the question.
and post the comment in the community support to refund your points.
0
 
DarthModCommented:
PAQed with points refunded (500)

DarthMod
Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now