Best way to find out publication in Multiple Publication agents?
-
Hi,
I was hoping someone could help me with some advice. I'm working with a SQL 2000 remote distributor that has about 60 Distribution Agents. Many of thise agents are made up of Multiple Publications, often large numbers of publications. Can anyone tell me what the fastest way would be to find which publications make up the agent?
For example, if I have an agent that pushes articles from a Customers database to five servers, and each server only subscribes to a few of the publications, and this DB has 20 published articles, rather than click on each Publication to see which server it goes to, is there a query or a central location I can see all of this information at once?
I hope what I am asking makes sense...if not I'll be happy to clarify.
-
I did get it... but you can make use of the following script...
drop
table #temp
set
nocount on
SELECT DISTINCT --'description' = substring(pub.[description],1, 25),
'publicationType' = case MPub.publication_type when 0 then 'Transactional'
when 1 then 'Snapshot'
WHEN 2 THEN 'Merge' end,
'Publisher' = @@servername,
'subscriber' = ss.srvname,
'publication' = pub.name,
'article' = art.name,
'destination database' = sub.dest_db,
'destination Tables' = art.dest_table,
'Subscription Type' = case sub.subscription_type when 0 then 'Push'
when 1 then 'Pull' end,
'subscription status' = case sub.status when 0 then 'InActive'
when 1 then 'Subscribed'
when 2 then 'Active' end,
'synchronization type' = case sub.sync_type when 1 then 'Automatic'
when 2 then 'None' end,
'Frequency' = CASE sjs.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'IntervalType'
= CASE freq_subday_type
when
1 then CASE len(active_start_time)
WHEN 3 THEN cast('00:0'
+ Left(right(active_start_time,3),1)
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(active_start_time,5),1)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
else '12:00:000'
END
when
2 then 'Seconds '
when
4 then 'Minutes '
when
8 then 'Hours '
End
,
'TimeInterval'
= CASE freq_subday_type
when
1 then 0
Else
convert(char(25),freq_subday_interval)
End
,
'StartTime' = CASE len(active_start_time)
WHEN 3 THEN cast('00:0'
+ Left(right(active_start_time,3),1)
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(active_start_time,5),1)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
else '12:00:000'
END,
'EndTime' = CASE len(active_end_time)
WHEN 3 THEN cast('00:0'
+ Left(right(active_end_time,3),1)
+':' + right(active_end_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(active_end_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(active_end_time,5),1)
+':' + Left(right(active_end_time,4),2)
+':' + right(active_end_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_end_time,6),2)
+':' + Left(right(active_end_time,4),2)
+':' + right(active_end_time,2) as char (8))
else '12:00:000'
END
INTO #TEMP FROM syssubscriptions sub,
master
..sysservers ss,
syspublications pub
,
distribution
..MSpublications Mpub, --syspublications pub,
sysextendedarticlesview art
,
msdb
..sysjobschedules sjs,
msdb
..sysjobs sj
WHERE UPPER(ss.srvname) LIKE UPPER('%') collate database_default
AND sub.srvid = ss.srvid
AND pub.name LIKE '%' collate database_default
AND art.name LIKE '%' collate database_default
AND art.pubid = pub.pubid
AND sub.artid = art.artid
AND (sub.login_name = suser_sname(suser_sid()) collate database_default OR
is_srvrolemember('sysadmin') = 1 OR
is_member ('db_owner') = 1)
AND MPUB.PUBLICATION = pub.name
AND sjs.job_id = pub.snapshot_jobid --sub.distribution_jobid --
AND sjs.job_id = sj.job_id
and sj.enabled = 1
--and sjs.enabled = 1
ORDER BY publicationType,subscriber, publication, article
UPDATE
#TEMP
SET
IntervalType = 'Continuous'
,
TimeInterval = null
where
publicationType = 'Transactional'
/*
select sjs.* from msdb..sysjobschedules sjs
join syspublications pub on
sjs.job_id = pub.snapshot_jobid
use ads
select * from syspublications
SELECT * FROM distribution..MSpublications
*/
select
* from #temp
MohammedU
Microsoft SQL Server MVP -
I'm getting these errors when I run them on the remote distributor:
Server: Msg 208, Level 16, State 1, Line 5
Invalid object name 'syssubscriptions'.
Server: Msg 208, Level 16, State 1, Line 5
Invalid object name 'syspublications'.
Server: Msg 208, Level 16, State 1, Line 5
Invalid object name 'distribution..MSpublications'.
Server: Msg 208, Level 16, State 1, Line 5
Invalid object name 'sysextendedarticlesview'.
-
Then qualify the errored table with server name.
remoteservername.distribution.dbo.tablename...
MohammedU
Microsoft SQL Server MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply