question

siera_gld avatar image
siera_gld asked

Query Report Server for Multiple procedures

I need to find a list of reports that use stored procedures with specific table. I have a list of stored procedures that i need to find which reports may have been developed using. I can use this select distinct [name], [path], cast(cast(content as varbinary(max)) as varchar(max)) from .ReportServer.dbo.Catalog where cast(cast(content as varbinary(max)) as varchar(max)) like '%%' this works if I want to check for 1 or two stored procedures. But I need to check for 280! how can i use my list of 280 and pass in those values in the like clause?
searchwildcard
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered
One way of doing it would be to create a table with just one column to store the value of your procedures, one record per proc\_name. Lets say that you have such table. In this case you can just join the two (not so sure that the query will be efficient, but it will work): select distinct c.[name], c.[path], cast(cast(c.[content] as varbinary(max)) as varchar(max)) [content] from ReportServer.dbo.Catalog c inner join your_db.dbo.your_table t on cast(cast(c.[content] as varbinary(max)) as varchar(max)) like '%' + t.your_column_name + '%'; Let me know if your list is not kept in the table, but is instead serialized as xml or a delimited string of procedure names, and I can add an appropriate snippet. <\!-- **Begin Edit** I have to edit my answer once more due to a small flaw in the script discovered as a result of the conversation in the comments to @Fathrjack's answer. The problem with applying .value directly to the xml content is that the method requires a singleton as its first parameter, so it will always return one record per report. This works most times, but if the report references more than one stored procedure then only the first one will show up in results. Additionally, if the first command text of the report using multiple procs/scrips is actually a plain valilla SQL statement then the result will not show any procedures at all. This needs to be addressed. The final query is listed below the original one (now marked as do not use). Here is the snippet you can use to get some useful information from the content column of the catalog table. Please note that this script will only work if the values in the Content column of the Catalog table are indeed convertible to valid xml. Since the actual data type of the Content column is image (they probably meant to design it as varbinary(max) but ended up with outdated image instead), it is possible that some of the column values are xml-looking, but do contain some invalid xml characters. In other words, it is possible that some of the values actually contain invalid xml. This can occasionally happen when there is a corrupted embedded picture in the actual report definition. Please modify the name of both ReportServer database and the name of the database where your procs callable by reports are actually stored, and the value of xmlns namespace to match yours (I only have SQL Server 2005 handy, so the default namespace of the reports is 2005 specific). Finally, I have to disclose that I simply stole Håkan Winther's idea to use the sys.procedures catalog view instead of worrying how to pass the list of the procedures to the query :) Here is the script (the one with the flaw described above, so don't use this one): with reports ([name], [path], DataSource, ProcName) as ( select [name], [path], (cast(cast([content] as varbinary(max)) as xml)).value( 'declare default element namespace " http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (Report/DataSets/DataSet/Query/DataSourceName)[1]', 'varchar(50)') DataSource, (cast(cast([content] as varbinary(max)) as xml)).value( 'declare default element namespace " http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (Report/DataSets/DataSet/Query/CommandText)[1]', 'varchar(50)') ProcName from ReportServer.dbo.Catalog ) select reports.[name], reports.[path], reports.DataSource, reports.ProcName from reports inner join your_db_name.sys.procedures procs on reports.ProcName = procs.name; In order to get as many records per report definition as ther are procedures/scripts it references, the guts of the xml\_content column must be first subjected to .nodes method which returns a collection of nodes. Once this is done, applying .value to each node of the collection will return desired results. The following query inserts relevant data to the temp table, much like in Fatherjack's blog. This appears to be a better alternative to a direct interrogation of the live Catalog table. Here is the script which works with SSRS 2005 (modify the xmlns value to the one specific to SSRS 2008 if needed) -- insert data into the temp table select [path], [name], convert(xml, convert(varchar(max),replace(convert( varbinary(max),[Content]),'', ''))) as xml_content into #ReportContents from ReportServer.dbo.Catalog where [Type] = 2; -- now query it: ;with xmlnamespaces( ' http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' as ns) select rc.[name], rc.[path], R.item.value('(ns:DataSourceName)[1]', 'varchar(100)') DataSource, R.item.value('(ns:CommandText)[1]', 'varchar(100)') CommandText, R.item.value('(ns:CommandType)[1]', 'varchar(100)') CommandType from #ReportContents rc cross apply rc.xml_content.nodes( '//ns:Report/ns:DataSets/ns:DataSet/ns:Query') R(item) inner join your_db_name.sys.procedures procs on R.item.value('(ns:CommandText)[1]', 'varchar(100)') = procs.name order by 1; **End Edit** --> Oleg
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you can call the join on such a terrible condition clean :) Take it back, you called it properly "clean" not clean. Actually, it is pretty shocking that the ReportServer database has a reserved word **Content** used as a column name and worse still the data type of that column is image, the very data type Microsoft strongly recommends to NEVER use as it will be deprecated in future version of the SQL Server. Amusingly, it just stores the report guts, so it is crying to be defined as varchar(max), but no, it is image.
1 Like 1 ·
+1 Your solution is "cleaner" :)
0 Likes 0 ·
You are right of course :)
0 Likes 0 ·
@Oleg, would Reg Gate's free tool, SQL Search, work here?
0 Likes 0 ·
Red Gate got me the list of procs by searching table names...it's cool - got me 80% there
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
You can add your code into a INLINE TABLE VALUED FUNCTION and use : select * from sys.procedures as p cross apply yourFunction(p.name)
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I got it by just copying / pasting the where script into excel and using a bunch of or's and then pasting it in the compiler - but how do i render the column that displays the xml without all of the xml fluff?
0 Likes 0 ·
but how do i render the column that displays the xml without all of the xml fluff?
0 Likes 0 ·
@siera_gld What do you want to display? The contents of the content column store the report definition, thus you get xml in your results. If you need a particular information from that xml, let me know and I can add a snippet to my answer.
0 Likes 0 ·
the sproc name, and db name if possible
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
What a coincidence, I wrote a blog article about this over the weekend - [ http://www.simple-talk.com/community/blogs/jonathanallen/][1] I hope it helps. [1]: http://www.simple-talk.com/community/blogs/jonathanallen/
8 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Fatherjack This is really cool. Now both SSRS 2005 and 2008 are covered between your blog and my answer :) At the risk of sounding stupid, I would like to note that both might have a small flaw (easy to fix). The scripts assume that there is only one procedure present in the report definition. I am not sure, but I believe that it is possible for a single report to have more than one proc referenced. For example, if there is a report with a couple dropdowns used to populate some list of values which will be used as procedure parameters then the report definition will have more than one procedure, and therefore, it would be a good idea to restate the .query().value() because .value() expects a singleton and in case of multiple procs, the script will reveal just the first one. This means that a cross apply might be needed, something like cross apply .nodes() and then .value(some_singleton) in the select part. This way, all procs will be included.
0 Likes 0 ·
@Oleg - I'm glad you like the blog. I will happily accede to your suggestion about multiple procedures in one report as my XML experience can be measured in hours rather than weeks or years. I will certainly test my code and if it fails make reference to it's shortfall on the blog. I am not sure what you mean about 2005 and 2008 - does my method not work on one? Have you seen the note at the foot about changing namespace for different servers?
0 Likes 0 ·
@Fatherjack Yes, of course, I also have the similar note about the namespace accompanying my query. I only meant to say that the actual 2008-specific xmlns value is ***already spelled out*** in your query and the actual 2005-specific xmlns value - in mine, that is all. As far as the flaw is concerned: neither of the scripts will fail to run, they would rather fail to "find" all the procedures in the report definition should the latter have more than one. I can try to find one definition like this and then alter my query a bit to reveal the behaviour.
0 Likes 0 ·
@Oleg - ah, I see! different defaults in mind!

I'll see what my query does and let you know how it responds. Would it be easier to resolve this via email and final solution back here?
0 Likes 0 ·
@Fatherjack First Initial followed by entire last name at hotmail dot com
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.