February 8, 2010 at 1:24 pm
Cris E (2/8/2010)
It occurs on reports where there are no data sources used (like a REPORTS ARE UNAVAILABLE report we put up) and the ChARINDEX functions both return 0.To fix it replace the second SELECT with this:
[font="Courier New" size="2"]selectitemID,
[name],
[path],
case when charindex('<commandText>',reportText) = 0 or charindex('</commandText>',reportText) = 0 then '(no query)'
else substring(reportText, charindex('<commandText>',reportText)+13, charindex('</commandText>',reportText)-charindex('<commandText>',reportText)-13) end as commandText
fromRS[/font]
EDIT: OK, the formatting experience is a bit frustrating today...
Thanks Cris,
But I am getting the same error (when I preview the report):
An error occurred during local report processing.
An error occurred during report processing.
Cannot read the next data row for the data set ReportQueries.
XML parsing: line 90, character 10, illegal XML character.
when I query the report server database, it gives me:
Msg 9420, Level 16, State 1, Line 3
XML parsing: line 90, character 10, illegal xml character
Any ideas ... I can run the reports on my local laptop but not on Prod box.
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
February 8, 2010 at 1:28 pm
That is something I stated it does (display the first query used). Eventually I'll build a section (probably using a CTE) to concatenate all of the queries together.
February 8, 2010 at 1:30 pm
michael_davis2 (2/8/2010)
That is something I stated it does (display the first query used). Eventually I'll build a section (probably using a CTE) to concatenate all of the queries together.
Thanx. I must have glossed over that part.
February 8, 2010 at 1:33 pm
No problem - I'll notify you directly (with another reply) when this is done.
February 9, 2010 at 12:41 pm
I am receiving the same error.
I have taken the code and created the necessary stored procedures (our work does not allow anything else) and have everything working great (this suite of reports are fantastic btw!) except the Report Queries report.
The offending piece of code seems to be the FROM sub query, because it freaks out if I only run that piece
The;
SELECT
itemID,
[name],
[path],
CAST(CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS Varchar(max)) AS reportText
FROM
CMHDBRPT.CIGReportServer.dbo.[catalog]
WHERE
[Type] = 2
AND (@reportID = convert(varchar(500),itemID) or @reportID = 'NOID')
Any assistance in getting this to work would be fantastic.
The query runs fine for a single parameterized reportID, but if you run it with 'NOID' to return all reports, this is when it errors.
Error is;
Msg 9420, Level 16, State 1, Procedure upRptRSReportQueries, Line 43
XML parsing: line 2132, character 10, illegal xml character
TIA
February 12, 2010 at 11:07 am
Have you looked at SQLScrubs for SSRS 2008? It provides the ETL, DW and reports to do a lot of this. And it's free on codeplex.
Gets around the 60 day limitations, and with the added metrics available in SSRS 2008, allows for richer analysis and optimization of trouble zones in reporting.
April 19, 2010 at 3:00 pm
Please send me .RDL for "All Report Parameters".
Thank you.
May 20, 2010 at 9:42 am
Very nice Report Davis. This was exactly what I was looking for and much more :)!
Excellent work!
May 20, 2010 at 9:45 am
David-Leibowitz (2/12/2010)
Have you looked at SQLScrubs for SSRS 2008? It provides the ETL, DW and reports to do a lot of this. And it's free on codeplex.Gets around the 60 day limitations, and with the added metrics available in SSRS 2008, allows for richer analysis and optimization of trouble zones in reporting.
is there a SQL 2005 version yet? we were looking to buy it but aren't running 2008 yet
May 21, 2010 at 4:03 pm
This is fantastic. We have been using it for a few months, and it beats using RSExecutionLogs database.
It seems there is a limitation on the command text though. It is not returning all the sql in the report.
I am faced with parsing the sql in 20 reports manually or view the rdl files in a text editor or use this report to find the specific sql i need. I need to put a where clause in the query to bring back where [content] LIKE '%tbOVP%'
Any suggestions? Seems to be a problem with the sql below:
set transaction isolation level read uncommitted
;with RS as
(
selectitemID,
[name],
[path],
CAST(CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS Varchar(max)) AS reportText
from[catalog]
where[Type] = 2
and (@reportID = convert(varchar(500),itemID) or @reportID = 'NOID')
)
selectitemID,
[name],
[path],
substring(reportText, charindex('<commandText>',reportText)+13, charindex('</commandText>',reportText)-charindex('<commandText>',reportText)-13) as commandText
fromRS
May 21, 2010 at 6:41 pm
Thanks -- the query you mention does have a limitation as it looks for the first instance of "<CommandText>" and "</CommandText>" to use as start and end locations -- but nothing else beyond it. I'll take a look at this to see if I can help you out, though.
May 21, 2010 at 7:45 pm
Thanks. It does seem to not bring back all the query text.
May 25, 2010 at 10:03 pm
Hi Davis,
This is an excellent article. It helped me a lot.
But I just wanted to know one more thing.
I am creating a subscriptions report that lists all the subscriptions.
and I also wanted to know their frequency i.e whether they are weekly subscriptions or fortnightly subscriptions or monthly subscriptions or qtrly etc.........
So is there any field in the Report Server Database tables which displays the above information.
Thanks,
grkanth81
May 26, 2010 at 9:55 am
Try this.
SELECT Catalog.Name, Catalog.Path, Schedule.NextRunTime, Schedule.LastRunTime, Schedule.StartDate, Schedule.RecurrenceType,
Schedule.MinutesInterval, Schedule.DaysInterval, Schedule.WeeksInterval, Schedule.DaysOfWeek, Schedule.DaysOfMonth, Schedule.Month,
Schedule.MonthlyWeek
FROM Subscriptions INNER JOIN
Catalog ON Subscriptions.Report_OID = Catalog.ItemID INNER JOIN
ReportSchedule ON Subscriptions.SubscriptionID = ReportSchedule.SubscriptionID AND Catalog.ItemID = ReportSchedule.ReportID INNER JOIN
Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
June 2, 2010 at 11:39 am
joe...
sorry for the delay - been working on a large project deployment. anyway, i updated the command text report to display multiple queries (should the report have more than one dataset). usually sqlServerCentral posts updates quickly - but just in case, below is the new query.
thanks again for your positive feedback. hope all is well and let me know if you have any questions...
set transaction isolation level read uncommitted
selectitemID,
name,
path,
replace(replace(CAST(CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS Varchar(max)),'>','>'),'<','<') as reportText
into#catalog
fromcatalog
whereCAST(CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS Varchar(max)) like '%<commandText>%'
;with myCTE as (
selectitemID,
name,
path,
start,
finish,
len(reportText) as total,
substring(reportText, start, (finish-start)) as commandText,
right(reportText,len(reportText)-finish) as reportText
from(
selectc1.itemID,
c1.name,
c1.path,
charindex('<commandText>',c1.reportText) as start,
charindex('</commandText>',c1.reportText) as finish,
c1.reportText
from#catalog c1
) a
union all
selectitemID,
name,
path,
charindex('<commandText>',reportText) as start,
charindex('</commandText>',reportText) as finish,
total,
substring(reportText, (charindex('<commandText>',reportText)), ((charindex('</commandText>',reportText))-(charindex('<commandText>',reportText)))),
right(reportText,len(reportText)-(charindex('</commandText>',reportText)))
from(
selectitemID,
name,
path,
start,
finish,
total,
reportText
frommyCTE
) x
wherecharindex('<commandText>',reportText) > 0
)
--Final Select
selectitemID,
name,
path,
replace(commandText,'<commandText>','') as commandText
frommyCTE
order byitemID
--Cleanup
drop table#catalog
Viewing 15 posts - 61 through 75 (of 117 total)
You must be logged in to reply to this topic. Login to reply