Subscriptii reporting services

Configurare noua (How To)

Situatie

Mai jos aveti o procedura care ruleaza pe baza ReportServer de pe un server sql si care returneaza o lista cu subscriptiile ce sunt programate in reporting services. Printre altele returneaza mailul persoanei catre care pleaca subscriptia, ultimul status al subscriptiei, formatul subscriptiei, urmatoarea data de rulare, s.a.m.d.

Solutie

alter procedure SubscriptiiReportingServices_prc

as

SELECT

[InactiveFlags],
‘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=”RenderFormat”])[1]’,’nvarchar(50)’) as [Render Format]
,Convert(XML,[ExtensionSettings]).value(‘(//ParameterValue/Value[../Name=”Subject”])[1]’,’nvarchar(50)’) as [Subject]
—Example report parameters: StartDateMacro, EndDateMacro & Currency.
,Convert(XML,[Parameters]).value(‘(//ParameterValue/Value[../Name=”StartDateMacro”])[1]’,’nvarchar(50)’) as [Start Date]
,Convert(XML,[Parameters]).value(‘(//ParameterValue/Value[../Name=”EndDateMacro”])[1]’,’nvarchar(50)’) as [End Date]
,Convert(XML,[Parameters]).value(‘(//ParameterValue/Value[../Name=”Currency”])[1]’,’nvarchar(50)’) as [Currency]
,[ModifiedDate]
,S.[Description]
,[LastStatus]
,[EventType]
,[LastRunTime]
,[DeliveryExtension]
,[Version]
FROM [ReportServer].[dbo].[Subscriptions] S
INNER JOIN ReportServer.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

 

order by 2

Tip solutie

Permanent
Etichetare:

Voteaza

(18 din 29 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?