38 lines
1.3 KiB
SQL
38 lines
1.3 KiB
SQL
SELECT srv.name,*
|
|
FROM
|
|
(
|
|
SELECT *
|
|
FROM distribution..MSdistribution_agents agents
|
|
WHERE subscriber_db IN ( 'Gaia' )
|
|
AND anonymous_subid IS NULL
|
|
) sub1
|
|
INNER JOIN
|
|
(
|
|
SELECT publisher,
|
|
publisher_db,
|
|
publication,
|
|
publication_type,
|
|
agent_name,
|
|
publisher_srvid,
|
|
job_id
|
|
FROM distribution..MSreplication_monitordata
|
|
WHERE publication_id IS NOT NULL
|
|
AND agent_type = 3
|
|
) sub3
|
|
ON sub1.publisher_id = sub3.publisher_srvid
|
|
--AND CAST(sub1.job_id AS UNIQUEIDENTIFIER) = sub3.job_id
|
|
AND sub1.publisher_db = sub3.publisher_db
|
|
AND sub1.publication = sub3.publication
|
|
AND sub1.subscription_type = sub3.publication_type
|
|
AND sub1.name = sub3.agent_name
|
|
JOIN master.sys.servers AS srv
|
|
ON srv.server_id = sub1.subscriber_id;
|
|
|
|
SELECT *
|
|
FROM sys.servers
|
|
|
|
SELECT GAIASrvName = CASE WHEN [Type] = 'DEVE' THEN 'SWGCMDEV01.CENTRALINFRA.NET\DGALCTP'
|
|
WHEN [Type] = 'VALI' THEN 'SWGCMQMS01.CENTRALINFRA.NET\TGALCTP'
|
|
WHEN [Type] = 'PROD' THEN 'SWGCMDB01.CENTRALINFRA.NET\PGALCTP'
|
|
ELSE '' END FROM [master].[cfg].[InstanceContext]
|