CODEWITHSUNDEEP
sql-serverstored-proceduresreporting-servicesreportviewerreportserver
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10247

How can I determine the Stored Proc used on an SSRS page?

We have an SSRS Report Server (I don't know if the name "Report Sever" is peculiar to us or common to SQL Server report services (IOW, I don't know if we chose it, or Microsoft did)) that hosts various queries that users can run against our data.

I need to find out which query is being run, probably which Stored Proc, for a given report that is available.

I thought I could suss that out via the "View Source" from the page, but I see no "data source," nor the name of our connection, or such. In fact, I looked through the entire source and saw nothing helpful.

The "View Report" button is:

<td><input type="submit" name="ReportViewerControl$ctl04$ctl00" value="View Report" id="ReportViewerControl_ctl04_ctl00" />

...and the only other reference to it is here:

Sys.Application.add_init(function() {
    $create(Microsoft.Reporting.WebFormsClient._PromptArea, {"CredentialsLinkId":"ReportViewerControl_ctl04_ctl01","ParameterIdList":["ReportViewerControl_ctl04_ctl03","ReportViewerControl_ctl04_ctl05","ReportViewerControl_ctl04_ctl07","ReportViewerControl_ctl04_ctl09"],"ParametersGridID":"ParametersGridReportViewerControl_ctl04","ReportViewerId":"ReportViewerControl","ViewReportButtonId":"ReportViewerControl_ctl04_ctl00"}, null, null, $get("ReportViewerControl_ctl04"));
});

Is there a way, either from the "View Source" or otherwise, where I can coax out the SP used to generate the report?

UPDATE

Spurred on by Steve Mangiameli's comment, I see that I have the following in my Start menu:

MS SQL Server 2005
    SQL Server Business Intelligence Development Studio (BIDS)
MS SQL Server 2008
    SQL Server Installation Center
MS SQL Server 2012
    SQL Server Data Tools

I don't know which, of any, of these has value for me in this quest.

UPDATE 2

We do have a Visual Studio 2005 project named "Report Server" project with many .rdl files beneath the "Reports" folder. I thought this was a unique-to-us solution, but maybe not.

Even if this is the "Report Server" mentioned, though, I still don't know which .rdl file is the one that drives the report I can generate via our online Report Viewer page.

It would be nice if the report, when generated, had some reference to the name of the related .rdl file.

Upvotes: 4

Views: 5385

Answers (2)

Steve Mangiameli
Steve Mangiameli

Reputation: 688

You're going to want to navigate either Report Manager or the Report Server to download the report. Once you have the .rdl file, open it in a text editor...it's just an xml file. Simply perform a find for "dataset" and inspect the dataset values. You will see the datasource, parameter and SP. Use this link to help you navigate the site.

Adding a better link with pictures. I'd do it myself but don't have access to cloud services here at work.

http://sql-articles.com/articles/general/download-export-rdl-files-from-report-server/

Upvotes: 3

Chris Latta
Chris Latta

Reputation: 20560

The easiest way is to use Sql Profiler. Run Sql Profiler, put a trace on your Sql Server and run your report. You'll then see the exact SQL statement that is hitting the server.

Upvotes: 3

Related Questions