SSRS Report Schedule - getting info from the DB - What do these numbers mean ? -
- mw112009 - Thursday, April 20, 2017 2:36 PM
You would just need to select x.y.value('(Days/text())[1]',INT) would you not? Same way you got the StartDateTime.
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 - Thursday, April 20, 2017 2:46 PM
You were close.. Thx.. But this worked...
x.y.value('(MonthlyRecurrence/Days/text())[1]','INT')
-
You keep changing the XML. In one post it has a namespace, in the next it has an xsd.
Based on the latest sample, this will 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: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>';SELECT
[StartDateTime] = x.y.value('(StartDateTime/text())[1]', 'DATETIMEOFFSET'),
[StartHour] = DATEPART(HOUR, x.y.value('(StartDateTime/text())[1]', 'DATETIMEOFFSET')),
[StartMin] = DATEPART(MINUTE, x.y.value('(StartDateTime/text())[1]', 'DATETIMEOFFSET')),
[Days] = x.y.value('(MonthlyRecurrence/Days/text())[1]', 'INT'),
[January] = x.y.value('(MonthlyRecurrence/MonthsOfYear/January/text())[1]', 'BIT')
FROM @xml.nodes('/ScheduleDefinition') x(y);Why do you keep extracting all the values as varchar(???). Use the correct data types. SQL is not good at manipulating strings.
-
Listing 1 works
Listing 2 - I amnot getting a value for the columm January. I know for sure there is a report out there where the <JANUARY>true</JANUARY> exists for sure..
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>false</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]= x.y.value('(MonthlyRecurrence/Days/text())[1]','INT'),
[January] = x.y.value('(MonthlyRecurrence/MonthsOfYear/January/text())[1]','VARCHAR(500)'),
[February] = x.y.value('(MonthlyRecurrence/MonthsOfYear/February/text())[1]','VARCHAR(500)'),
[March] = x.y.value('(MonthlyRecurrence/MonthsOfYear/March/text())[1]','VARCHAR(500)'),
[April] = x.y.value('(MonthlyRecurrence/MonthsOfYear/April/text())[1]','VARCHAR(500)'),
[May] = x.y.value('(MonthlyRecurrence/MonthsOfYear/May/text())[1]','VARCHAR(500)'),
[June] = x.y.value('(MonthlyRecurrence/MonthsOfYear/June/text())[1]','VARCHAR(500)'),
[July] = x.y.value('(MonthlyRecurrence/MonthsOfYear/July/text())[1]','VARCHAR(500)'),
[August] = x.y.value('(MonthlyRecurrence/MonthsOfYear/August/text())[1]','VARCHAR(500)'),
[September] = x.y.value('(MonthlyRecurrence/MonthsOfYear/September/text())[1]','VARCHAR(500)'),
[October] = x.y.value('(MonthlyRecurrence/MonthsOfYear/October/text())[1]','VARCHAR(500)'),
[November] = x.y.value('(MonthlyRecurrence/MonthsOfYear/November/text())[1]','VARCHAR(500)'),
[December] = x.y.value('(MonthlyRecurrence/MonthsOfYear/December/text())[1]','VARCHAR(500)')FROM @xml.nodes('ScheduleDefinition') x(y)
-- I am not getting any data for the January column
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,
[StartHour]= SUBSTRING( (c.nd.value('(rs:StartDateTime/text())[1]', 'VARCHAR(500)')), 12, 2 ),
[StartMin]= SUBSTRING( (c.nd.value('(rs:StartDateTime/text())[1]', 'VARCHAR(500)')), 15, 2 ),
[January] = c.nd.value('(rs:MonthlyRecurrence/MonthsOfYear/January/text())[1]','VARCHAR(500)')-- provides a NULL
FROM getxml
CROSS APPLY ReportSchedule.nodes('//ScheduleDefinition') c(nd)
ORDER BY 2,1 - mw112009 - Thursday, April 20, 2017 3:11 PM
BTW the XML structure found in the matchdata column is the same what I have included in Listing 1
- mw112009 - Thursday, April 20, 2017 3:12 PM
You need to apply the namespace to each portion of the path to your data
[January] = x.y.value('(rs:MonthlyRecurrence/rs:MonthsOfYear/rs:January/text())[1]','BIT') - DesNorton - Thursday, April 20, 2017 3:21 PM
Great!, That worked..
- mw112009 - Thursday, April 20, 2017 2:24 PM
I will disagree with you on that. I can see the subscription specific details for the boxes that were selected. I have reports that have various different subscriptions and I can tell by looking at the output of the data what was selected for each report. From specific days, to specific months, to specific time intervals, days of month, days of week, and so forth. Looking at what you are attempting to do, you are trying to do the same thing that my script already covers.
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 3:35 PM
You are right. I got mixed up with bmgoo2 spost. Didnt notice the tiny url you had posted... YEs, I jujst ran the script and it gives me everything ( or else I can modify ) I need. Thanks. Never mind... good learning experience for me
-
See picture below you can clearly see there are more than 5 subscriptions
These were added today. Only 2 of them got executed ( They were there before today ). I did purposely edit the picture to take away the email addresses.
Never mind.. However when I run the following query it shows only a count of 2 for this particular report. Why is that ? Where is the other subscriptions stored ?
SELECT
CAT.[Name], count(*)
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
WHERE
CONVERT(CHAR(8), SUB.LastRunTime, 112 ) > '20170101'
GROUP BY CAT.[Name]
ORDER BY 1I should get a count of 7 don't you think ?
- mw112009 - Friday, April 21, 2017 12:55 PMSee picture below you can clearly see there are more than 5 subscriptions
These were added today. Only 2 of them got executed ( They were there before today ). I did purposely edit the picture to take away the email addresses.
Never mind.. However when I run the following query it shows only a count of 2 for this particular report. Why is that ? Where is the other subscriptions stored ?
SELECT
CAT.[Name], count(*)
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
WHERE
CONVERT(CHAR(8), SUB.LastRunTime, 112 ) > '20170101'
GROUP BY CAT.[Name]
ORDER BY 1I should get a count of 7 don't you think ?
Ahh I found the issue.. Just ignore this .. had to make a correction in the query
SELECT
CAT.[Name], count(*)
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
WHERE
CONVERT(CHAR(8), ISNULL(SUB.LastRunTime, GETDATE() ) , 112 ) > '20170101'
GROUP BY CAT.[Name]
ORDER BY 1
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply