December 1, 2006 at 8:15 pm
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
December 4, 2006 at 1:29 pm
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'.
December 4, 2006 at 9:02 pm
Then qualify the errored table with server name.
remoteservername.distribution.dbo.tablename...
MohammedU
Microsoft SQL Server MVP
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply