none
How to find profile_id of distribution agent? RRS feed

  • Question

  • I have transactional publications with different subscriptions. Some subscriptions are push, some pull. There are servers where distributor is on same server as publisher, there are two dedicated servers for distrubutors, and there is one case where distributor is located on subscriber.

    I need to find JobID of distribution agent for each subscription, and ID of profile it use, and do it automatically. First part, with jobID, was solved. I try to find ID of profile for each distribution job. 

    First I tryed to use MSdistribution_agents table, it have Profile_id field. But seems as JobID in the table differ from JobID from msdb..sysjobs, at least for pull subscriptions. I looked both on publisher (act as its own distrubutor) and on subscriber. I see job on subscriber in msdb..sysjobs (pull subscription), but there is no entry in distribution..MSdistribution_agents for the job. Checked publisher - there is no entry with such jobID too in MSdistribution_agents. Next, looked how Replication Monitor find the information. It use some undocumented stored procedures. So, is any way to find profile ID for each distribution agent with usage of only documented tables and stored procedures?

    Saturday, July 21, 2012 1:07 PM

Answers

  • With pull subscriptions the distribution agent job will not be local to the Distributor.  So whatever job_id is in MSdistribution_agents for a pull subscription is useless for querying against msdb..sysjobs at the Distributor.

    For pull subscriptions try this at the Subscriber:

    SELECT 
    	sj.job_id, 
    	sj.originating_server_id,
    	sj.name,
    	sj.enabled,
    	sj.description,
    	sj.start_step_id,
    	sj.category_id,
    	sj.owner_sid,
    	sj.notify_level_eventlog,
    	sj.notify_level_email,
    	sj.notify_level_netsend,
    	sj.notify_level_page,
    	sj.notify_email_operator_id,
    	sj.notify_netsend_operator_id,
    	sj.notify_page_operator_id,
    	sj.delete_level,
    	sj.date_created,
    	sj.date_modified,
    	sj.version_number
      FROM MSsubscription_properties sp
      JOIN msdb.dbo.sysjobsteps sjs ON sp.job_step_uid = sjs.step_uid
      JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id


    Brandon Williams (blog | linkedin)

    Sunday, July 22, 2012 8:04 AM
    Moderator

All replies

  • Hi andsm,

    Do this query work for you?

    SELECT agent.id, agent.name, agent_profile.profile_id
    FROM distribution.dbo.MSdistribution_agents agent
    INNER JOIN msdb.dbo.MSagent_profiles agent_profile ON agent.profile_id = agent_profile.profile_id


    Brandon Williams (blog | linkedin)

    Saturday, July 21, 2012 9:24 PM
    Moderator
  • Hi Brandon ,

    The query works, but there is problem I describer earlier - unable to find corresponding job.

    There is no any entry with same MSdistribution_agents.job_id value in msdb..sysjobs.

    Following query return zero rows, if execute it either on distributor or subscriber:

    SELECT *
    FROM distribution.dbo.MSdistribution_agents a
    join msdb..sysjobs j on j.job_id = a.job_id

    Sunday, July 22, 2012 5:05 AM
  • With pull subscriptions the distribution agent job will not be local to the Distributor.  So whatever job_id is in MSdistribution_agents for a pull subscription is useless for querying against msdb..sysjobs at the Distributor.

    For pull subscriptions try this at the Subscriber:

    SELECT 
    	sj.job_id, 
    	sj.originating_server_id,
    	sj.name,
    	sj.enabled,
    	sj.description,
    	sj.start_step_id,
    	sj.category_id,
    	sj.owner_sid,
    	sj.notify_level_eventlog,
    	sj.notify_level_email,
    	sj.notify_level_netsend,
    	sj.notify_level_page,
    	sj.notify_email_operator_id,
    	sj.notify_netsend_operator_id,
    	sj.notify_page_operator_id,
    	sj.delete_level,
    	sj.date_created,
    	sj.date_modified,
    	sj.version_number
      FROM MSsubscription_properties sp
      JOIN msdb.dbo.sysjobsteps sjs ON sp.job_step_uid = sjs.step_uid
      JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id


    Brandon Williams (blog | linkedin)

    Sunday, July 22, 2012 8:04 AM
    Moderator