SSRS Report Schedule - getting info from the DB - What do these numbers mean ? -
-
Please see the attached output.... Can anyone re-write this query ( May be using some CASE statements withing SELECT ) and make it more meaningful ?
Certainly it is info about when the reports get run. But can it tell me something about the schedule ( Ex: Report A runs Weekly on M,T,W,TH,FRI at 10:10 AM )
USE [ReportServer]; -- You may change the database name.
GOSELECT
CAT.name, SCH.RecurrenceType,
SCH.WeeksInterval,SCH.DaysOfWeek,SCH.DaysOfMonth,SCH.Month,SCH.MonthlyWeek,SCH.State
--SUB.ExtensionSettings, SUB.Parameters, CAT.path,
--SCH.*
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR
ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS
ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH
ON RS.ScheduleID = SCH.ScheduleID
ORDER BY 1
-
If you want to know when the subscription is scheduled to be run, look at the SQL Agent Jobs. Those are what are triggered to start a subscription. The names are a bit strange, but I can shoot you a script to see what job corresponds to which report subscription if you like? Wrote it up a while back and just re-use it when needed... I'm sure somebody on the forum has it off the top of their head mind you.
Or do you NEED it from the table for something?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. - bmg002 - Wednesday, April 19, 2017 3:50 PM
Please shoot the script
-
Use this script. SSRS scheduling and agent scheduling via script really are kind of a pain in the butt.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Here ya go:
USE [ReportServer];
GO;--View all of the SSRS jobs
USE [ReportServer]
SELECT
[Schedule].[ScheduleID] AS [SQLAgent_Job_Name] ,
.[Description] AS [Description] ,
[Catalog].[Name] AS [Report Name] ,
[Catalog].[Path] AS [Report Path] ,
.[LastStatus] AS [Last Status] ,
[Schedule].[StartDate] AS [Subscription_Start_Date] ,
[Schedule].[EndDate] AS [Subscription_End_Date] ,
[Schedule].[LastRunTime] AS [Last Run Time]
FROM
[ReportSchedule] [ReportSchedule]
INNER JOIN [Schedule] [Schedule]
ON ( [ReportSchedule].[ScheduleID] = [Schedule].[ScheduleID] )
INNER JOIN [Subscriptions]
ON ( [ReportSchedule].[SubscriptionID] = .[SubscriptionID] )
INNER JOIN [Catalog] [Catalog]
ON (
[ReportSchedule].[ReportID] = [Catalog].[ItemID]
AND .[Report_OID] = [Catalog].[ItemID]
)
WHERE
[Catalog].[Name] LIKE '%' --Enter Report Name
AND .[Description] LIKE '%' --Enter an email or folder path
ORDER BY
.[LastStatus];Pretty easy to understand script. Just pull out the WHERE clause if you don't want to filter it. I got the script from a google search and did some tweaks to it myself.
EDIT - SQLRNNR has a better method as it does all the leg work for you. My method still requires you to do some work on your own or play in the GUI. His is a lot nicer.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. - bmg002 - Wednesday, April 19, 2017 4:13 PM
This particular script doesn't translate the schedules into anything very human friendly imho.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events - bmg002 - Wednesday, April 19, 2017 4:13 PM
LOL, we were caught posting at the same time.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events - SQLRNNR - Wednesday, April 19, 2017 4:15 PMSQLRNNR - Wednesday, April 19, 2017 4:17 PM
We did catch posting that at the same time. The post I did above is mostly to translate report name to sql agent job. Where I work, it is all we really do with it. If the subscription fails for any reason, we run that to find the job and re-run the job. We have a second part to that script where you paste in the job name and it'll run it too. But I didn't see much benefit in including that part.
EDIT - fingers work faster than brain. We fix what broke then re-run the job. It wouldn't make a lot of sense to re-run a thing if nothing was done to correct it, eh?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
OK, I am close, but I need some XML manipulation help. So the matchdata column in the SUBSCRIPTION table has all the schdule info. It is in XML. I need help on extracting the help.
Listing 1 - This XML works, I am able to grab parameters and values
Listing 2 - Works but I need help to grab the StartDate from the XML - Need your help - Please modify and reply.
Listing 3 - Sample XML found in the matchdata column
Listing 4 - Sample XML found in the matchdata column
WITH getxml AS
(
SELECT
SUB.SubscriptionID, SCH.* ,
ReportName = CAT.[Name],
ExtensionSettings =
CAST(ExtensionSettings as XML)
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR
ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS
ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH
ON RS.ScheduleID = SCH.ScheduleID
WHERE
CONVERT(CHAR(8), SUB.LastRunTime, 112 ) > '20170101')
SELECT
SubscriptionID,
ReportName,
[Name] = nd.value('(./Name)[1]', 'VARCHAR(8000)'),
[Value]= nd.value('(./Value)[1]', 'VARCHAR(8000)')FROM getxml
CROSS APPLY ExtensionSettings.nodes('/ParameterValues/ParameterValue') c(nd)
ORDER BY 2,1
--Listing 2 works but I am not able to extract the XML in the <StartDateTimeWITH getxml AS
(
SELECT
SUB.SubscriptionID,
ReportName = CAT.[Name],
ExtensionSettings =
CAST(ExtensionSettings as XML),
ReportSchedule = CAST( MatchData as XML )
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR
ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS
ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH
ON RS.ScheduleID = SCH.ScheduleID
WHERE
CONVERT(CHAR(8), SUB.LastRunTime, 112 ) > '20170101')
SELECT
SubscriptionID,
ReportName,
[StartDateTime] = nd.value('(./StartDateTime)[1]', 'VARCHAR(8000)'),--This shows NULL ?????
ReportSchedule
FROM getxml
CROSS APPLY ReportSchedule.nodes('/ScheduleDefinition') c(nd)
ORDER BY 2,1
Below is the sample data in the matchdata column
<ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StartDateTime xmlns="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">2015-04-23T08:00:00.000-04:00</StartDateTime>
<MonthlyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">
<Days>8</Days>
<MonthsOfYear>
<January>true</January>
<February>true</February>
<March>true</March>
<April>true</April>
<May>true</May>
<June>true</June>
<July>true</July>
<August>true</August>
<September>true</September>
<October>true</October>
<November>true</November>
<December>true</December>
</MonthsOfYear>
</MonthlyRecurrence>
</ScheduleDefinition>
<ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StartDateTime xmlns="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">2016-09-13T08:00:00.000-04:00</StartDateTime>
<WeeklyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">
<WeeksInterval>1</WeeksInterval>
<DaysOfWeek>
<Monday>true</Monday>
<Tuesday>true</Tuesday>
<Wednesday>true</Wednesday>
<Thursday>true</Thursday>
<Friday>true</Friday>
</DaysOfWeek>
</WeeklyRecurrence>
</ScheduleDefinition> -
I am currently unable to test this solution,
But I believe the syntax to be correct.
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices' AS rs)
, getxml AS (
SELECT
SUB.SubscriptionID,
ReportName = CAT.[Name],
ExtensionSettings = CAST(ExtensionSettings as XML),
ReportSchedule = CAST( MatchData as XML )
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR
ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS
ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH
ON RS.ScheduleID = SCH.ScheduleID
WHERE CONVERT(CHAR(8), SUB.LastRunTime, 112 ) > '20170101'
)
SELECT
SubscriptionID,
ReportName,
[StartDateTime] = c.nd.value('(rs:StartDateTime/text())[1]', 'DATETIMEOFFSET')
ReportSchedule
FROM getxml
CROSS APPLY ReportSchedule.nodes('//ScheduleDefinition') c(nd)
ORDER BY 2,1 - DesNorton - Thursday, April 20, 2017 1:02 PM
you had missed a comma, But it worked fine..... So may I ask why the one below is giving me a NULL.
Note: if I replace the [xmlns=] with [xmlns:xsd=] then it works.. Unfortunately the XML data I get is from the SUBSCRIPTION table ( I can not update it ) . So is there a way to make this XML work
declare @xml xml
SET @xml =
'<ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StartDateTime xmlns="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">2015-04-23T08:00:00.000-04:00</StartDateTime>
<MonthlyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">
<Days>8</Days>
<MonthsOfYear>
<January>true</January>
<February>true</February>
<March>true</March>
<April>true</April>
<May>true</May>
<June>true</June>
<July>true</July>
<August>true</August>
<September>true</September>
<October>true</October>
<November>true</November>
<December>true</December>
</MonthsOfYear>
</MonthlyRecurrence>
</ScheduleDefinition>'SELECT
x.y.value('(StartDateTime/text())[1]', 'VARCHAR(max)') as 'StartDateTime'
FROM @xml.nodes('ScheduleDefinition') x(y) - mw112009 - Thursday, April 20, 2017 1:44 PM
No problem got it to work.... Thanks.......
declare @xml xml
SET @xml =
'<ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StartDateTime xmlns:xsd="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">2015-04-23T08:00:00.000-04:00</StartDateTime>
<MonthlyRecurrence xmlns:xsd="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">
<Days>8</Days>
<MonthsOfYear>
<January>true</January>
<February>true</February>
<March>true</March>
<April>true</April>
<May>true</May>
<June>true</June>
<July>true</July>
<August>true</August>
<September>true</September>
<October>true</October>
<November>true</November>
<December>true</December>
</MonthsOfYear>
</MonthlyRecurrence>
</ScheduleDefinition>'
;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices' AS rs)
SELECT
[StartDateTime] = x.y.value('(rs:StartDateTime/text())[1]', 'DATETIMEOFFSET'),
x.y.value('(StartDateTime/text())[1]', 'VARCHAR(max)') as 'StartDateTimeNotWorking'
FROM @xml.nodes('ScheduleDefinition') x(y) -
I don't see why you needed to spend so much more time on it. I provided a solution that already grabbed the schedules for you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events - SQLRNNR - Thursday, April 20, 2017 1:54 PM
Your script does not give me the exact check boxes that the user selected when creating the schedule. So have to use XML
-
The one below works..... Can someone help me extract the [days] and [January]... please
declare @xml xml
SET @xml =
'<ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StartDateTime xmlns:xsd="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">2015-04-23T08:00:00.000-04:00</StartDateTime>
<MonthlyRecurrence xmlns:xsd="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">
<Days>8</Days>
<MonthsOfYear>
<January>true</January>
<February>true</February>
<March>true</March>
<April>true</April>
<May>true</May>
<June>true</June>
<July>true</July>
<August>true</August>
<September>true</September>
<October>true</October>
<November>true</November>
<December>true</December>
</MonthsOfYear>
</MonthlyRecurrence>
</ScheduleDefinition>'
;
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices' AS rs)
SELECT
x.y.value('(StartDateTime/text())[1]', 'VARCHAR(max)') as 'StartDateTime',
[StartHour]= SUBSTRING( (x.y.value('(StartDateTime/text())[1]', 'VARCHAR(500)')), 12, 2 ),
[StartMin]= SUBSTRING( (x.y.value('(StartDateTime/text())[1]', 'VARCHAR(500)')), 15, 2 ),
[Days]='8',--Please provide how to extract
[January]='true'--Please provide how to extract
FROM @xml.nodes('ScheduleDefinition') x(y)
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply