Subscribe To

Subscribe to SQL Server and XML by Email
Labels

Wednesday, January 16, 2008

How to find all stored procedures used by Report Server?

Moved to http://beyondrelational.com/blogs/jacob/archive/2008/01/16/how-to-find-all-stored-procedures-used-by-report-server.aspx

16 comments:

  1. Anonymous6:10 PM

    Good stuff. Add the following to stop it failing when it comes across an embedded image file...

    WHERE NOT patindex('%.%', name) > 0

    ReplyDelete
  2. Anonymous6:34 PM

    Thanks! This reduced a lot of work for me! /Sven

    ReplyDelete
  3. Hi. I'm getting an error when I run this:
    Msg 9403, Level 16, State 1, Line 1
    XML parsing: line 0, character 0, unrecognized input signature

    I admit to knowing nothing about XML, so I'm not quite sure what could be the problem. Any ideas?

    Thanks!

    ReplyDelete
  4. Hi. I'm getting an error when I run this:
    Msg 9403, Level 16, State 1, Line 1
    XML parsing: line 0, character 0, unrecognized input signature

    I admit to knowing nothing about XML, so I'm not quite sure what could be the problem. Any ideas?

    Thanks!

    ReplyDelete
  5. Never mind my previous comment - I missed missed where you said to change to VARCHAR(max).

    Thanks!

    ReplyDelete
  6. Argh! I spoke too soon. Even with the VARCHAR(max), I get "Msg 9403, Level 16, State 1, Line 1
    XML parsing: line 0, character 0, unrecognized input signature"

    ReplyDelete
  7. I doubt you have a spelling mistake, specifically in the part where the namespace is declared. Could you post the code you are running and I will try to spot if there is something wrong with it.

    ReplyDelete
  8. I ran the code from the blog:
    ;WITH XMLNAMESPACES (


    DEFAULT
    'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd

    )
    SELECT


    name,
    x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
    x.value('CommandText[1]','VARCHAR(50)') AS CommandText

    FROM (


    select name,
    CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
    from ReportServer.dbo.Catalog

    ) a
    CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)

    ReplyDelete
  9. The syntax looks correct.

    Are you sure that you are connected to an instance of SQL Server 2005? The code provided in this post uses XQuery that is available only on SQL Server 2005 and later versions.

    ReplyDelete
  10. Yes, we're definitely on SQL Server 2005. The code works on our dev box, but not in production. In production, it returns a partial result set (about 200 records, but I would expect well over 700 records) and the aforementioned error.

    ReplyDelete
  11. Did you try applying the filter suggested by "anonymous" at the top of the comment list?

    ReplyDelete
  12. Hi Jacob. I did apply the filter suggested. I am now trying to use the query below, which tweaks yours slightly to add the data source and data set (obtained from http://tejasnshah.wordpress.com/2009/02/21/sql-server-how-to-stored-procedure-names-used-by-reports-sql-reporting-services/). However, I am still getting the same error (Msg 9403, Level 16, State 1, Line 2
    XML parsing: line 0, character 0, unrecognized input signature). It bombs out at the same number of records as your original query, returning results for 135 reports (240 records total), although we actually have 262 reports.
    I've tried to research the error, but I'm not finding anything that is helping me understand it - probably due to my lack of knowledge of XML.

    Do you have any ideas of where I can look to try to decipher the error, as well as good resources basic xml queries? I would really appreciate any guidance.

    Here's the query:
    ;WITH xmlnamespaces (
    default
    'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
    )
    SELECT
    NAME AS reportname,
    x.value('DataSourceName[1]', 'VARCHAR(max)') AS datasourcename,
    q.value('@Name[1]', 'VARCHAR(max)') AS datasetname,
    x.value('CommandType[1]', 'VARCHAR(max)') AS CommandType,
    x.value('CommandText[1]','VARCHAR(max)') AS CommandText
    FROM
    (
    SELECT NAME,
    CAST(CAST(content AS varbinary(MAX)) AS xml) AS reportxml
    FROM reportserver.dbo.catalog
    ) a
    CROSS apply reportxml.nodes('/Report/DataSets/DataSet') d(q)
    CROSS apply q.nodes('Query') r(x)
    WHERE NOT patindex('%.%', name) > 0

    Note: when I execute SELECT @@VERSION, I get this:
    Microsoft SQL Server 2005 - 9.00.3186.00 (X64) Aug 11 2007 05:31:24 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) .

    ReplyDelete
  13. Marianne,
    I have written a collection of XQuery tutorials that might help you. You can find them here: http://blog.beyondrelational.com/2008/06/xquery-labs-collection-of-xquery-sample.html

    ReplyDelete
  14. Jacob,
    Thanks. I'll definitely check out that link

    ReplyDelete
  15. FYI - just in case anyone else runs into the same issue I had, I wanted to post the resolution. Once I limited the subquery to Type = 2 (for reports only), the query ran successfully.

    Here's the revised code:
    ;WITH xmlnamespaces (
    default
    'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
    )
    SELECT
    NAME AS reportname,
    x.value('DataSourceName[1]', 'VARCHAR(max)') AS datasourcename,
    q.value('@Name[1]', 'VARCHAR(max)') AS datasetname,
    x.value('CommandType[1]', 'VARCHAR(max)') AS CommandType,
    x.value('CommandText[1]','VARCHAR(max)') AS CommandText
    FROM
    (
    SELECT NAME,
    CAST(CAST(content AS varbinary(MAX)) AS xml) AS reportxml
    FROM reportserver.dbo.catalog
    where [type] = 2
    ) a
    CROSS apply reportxml.nodes('/Report/DataSets/DataSet') d(q)
    CROSS apply q.nodes('Query') r(x)
    WHERE NOT patindex('%.%', name) > 0

    ReplyDelete
  16. Marianne, Thanks for posting this.

    ReplyDelete

About Me
Jacob Sebastian
Tutorials
* XQuery Tutorials
My Links
SQL Server White Papers
SQL Server 2008
My Articles
XML Workshop RSS Feed
Contact me
Readers
Free Hit Counter
Web Site Hit Counters
SQL Server Bloggers
Blog Directories
blogarama - the blog directory Programming Blogs - BlogCatalog Blog Directory
 
Copyright Jacob Sebastian