How to find query/stored procedure used in an SSRS report

If yor are troubleshooting an SSRS report which you have not created by yourself. Finding it’s location may take sometime even though you know that issue is with the query. Hence to find the query/stored procs used in the report we can directly query ReportServer database and get those details from it’s report dataset.

SELECT Name As ReportName,
CAST(CAST(Content AS VARBINARY(MAX)) AS XML) AS ReportInXMLFormat
FROM ReportServer.dbo.Catalog
where Type = 2 
order by Name

it will give an XML result which will look like this.

<DataSets>
    <DataSet Name="EmployeeDetails">
        <Query>
            <CommandType>StoredProcedure</CommandType>
            <CommandText>usp_getEmpDetails</CommandText>
            <QueryParameters>
                <QueryParameter Name="@EMPLOYEEID">
                    <Value>=Parameters!EMPID.Value</Value>
                </QueryParameter>               
            </QueryParameters>
            <DataSourceName>DVEMP02</DataSourceName>
        </Query>
        .
        .
        .
   </DataSet>
    <DataSet Name="Department">
        <Query>
            <DataSourceName>DVEMP02</DataSourceName>
            <CommandText>
                SELECT DepartmentId AS ID, DeparmentName 
                FROM Department               
            </CommandText>
            <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
        </Query>
    </DataSet>
    .

Query tag will tel you about the sql query and stored proc used in the reports. You can start troubleshooting right away if it’s a data or a query issue.

I hope it helps!

Advertisements

About saxenapraveen

I work with Microsoft and have 12 years of experience in developing,designing and leading reliable and scalable solutions for cloud and device ready businesses.

Posted on April 4, 2013, in Sql Server, SSRS and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: