Prev: I would like a replication that not expired
Next: Replication broken, need to delete records from replication. Help.
From: Thomas Hase on 20 Nov 2009 09:37
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
From: Paul Ibison on 22 Nov 2009 07:37
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
From: Thomas Hase on 23 Nov 2009 13:00
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
From: Paul Ibison on 23 Nov 2009 13:30
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
From: Thomas Hase on 23 Nov 2009 14:05
Hallo Paul,

thanks, I haven't 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
 |  Next  |  Last
Pages: 1 2
Prev: I would like a replication that not expired
Next: Replication broken, need to delete records from replication. Help.