SQLsnippets/SSRS_Subscriptions FUN.sql at master · technoherder/SQLsnippets · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68


-- lists subscriptions

SELECT
    S.[SubscriptionID]
    ,C.[Name] + ' (' + S.[Description] + ')' AS [DisplayName]

FROM [dbo].[Subscriptions] S
INNER JOIN [dbo].[Catalog] C ON S.[Report_OID] = C.[ItemID]

WHERE SubscriptionID IN ('Subscription ID')

ORDER BY [DisplayName]

--  take that subscription id and tell report servr to run it
EXEC [dbo].[AddEvent] 'TimedSubscription', 'Subscription ID';  -- @EventData;

-- check last status
SELECT
    S.[Description] AS [SubscriptionDescription]
    ,C.[Name] AS [ReportName]
    ,S.[DeliveryExtension]
FROM [dbo].[Subscriptions] S
INNER JOIN [dbo].[Catalog] C ON S.[Report_OID] = C.[ItemID]
WHERE
    S.[SubscriptionID] = 'Subscription ID'

	-- ssrs info
SELECT
c.Name AS ReportName,
'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Next Run Time' = isnull(CASE len(next_run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,'NA'),
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="CC"])[1]','nvarchar(50)') as [CC]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]
,[LastStatus]
,[EventType]
,[LastRunTime]
,[DeliveryExtension]
FROM
 dbo.[Catalog] c
INNER JOIN dbo.[Subscriptions] S ON c.ItemID = S.Report_OID
INNER JOIN dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id
where
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') like '%Subject Namr%'