Howto find the Name of Logreader and Distribution Task

Hi NG,

MSSQL2k8 Transactional Replication

How can I find with T-SQL for a known publication
the name or ID of the related task for logreader-task, snapshot-task
and distribution task, if the task was manually renamed.

Thanks in advanced.

Thomas

Hi Thomas,here are the queries you need:distribution agents (assuming

Hi Thomas,

here are the queries you need:

distribution agents (assuming push):

select a.job_id, a.name as OldName, b.name as CurrentName
from distribution.dbo.MSdistribution_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id

log reader agents:

select a.job_id, a.name as OldName, b.name as CurrentName
from distribution.dbo.MSlogreader_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id

snapshot agents:

select a.job_id, a.name as OldName, b.name as CurrentName
from distribution.dbo.MSsnapshot_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id

HTH,

Paul Ibison

Hallo Paul,thanks it works, but my question was wrong.

Hallo Paul,

thanks it works, but my question was wrong.

On the MSSQL are many (10) publications.

Pub01
Pub02
..
Pub10


An I want to find VIA the name of publication all
related jobs.


exec sp_show_me_all_related_jobs 'pubName'

I am looking for a way to find out

Thomas

You can adapt my queries using a union statement as follows:select a.

You can adapt my queries using a union statement as follows:

select a.* from
(
select a.publication, b.name as CurrentJobName
from distribution.dbo.MSdistribution_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id
union
select a.publication, b.name as CurrentJobName
from distribution.dbo.MSlogreader_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id
union
select a.publication, b.name as CurrentJobName
from distribution.dbo.MSsnapshot_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id
) a
where a.publication = 'yourpubname'

This works for transactional. If you need merge then let me know.

HTH,

Pual Ibison

Hallo Paul,thanks, I have not seen the collumn "publication".If I try:select A.

Hallo Paul,

thanks, I have not seen the collumn "publication".


If I try:

select A.PUBLICATION, a.job_id, a.name as OldName, b.name as
CurrentName
from distribution.dbo.MSlogreader_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id

then I get in column: publication -> "ALL"  instead the name of the
publication


ALL	0x8E585BD4349ABC45B8DB66C8A540974A	ST-BB-SRV2-DB1-7 ..
ALL	0x9EF19737EBB26D45BAB1A3013CF3D09F	ST-BB-SRV2-DB2-6 ..


Is this a missconfig of my replication?

Thomas

Only applies to the log reader agent - they do not belong to a

Only applies to the log reader agent - they do not belong to a particular
publication - just a transactionally replicated database.
The other ones will give the publication name.
HTH,
Paul Ibison

Hallo Paul,thank youThomas

Hallo Paul,



thank you


Thomas