4

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.

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
  • 1
    Report Servers typically are accompanied with a corresponding Report Manager site. The report can either be opened in ReportDesigner within Report Manager or you can download and open in BIDS or Visual Studio to inspect the elements. View Source will not give you anything you can use to find the DB code used. – Steve Mangiameli Dec 10 '15 at 22:44
  • It is the site I'm looking at; I know nothing of Report Designer or Report Manager. How can I download what I need, and know what I need? Visual Studio would be perfect, but I don't know what to grab. – B. Clay Shannon-B. Crow Raven Dec 10 '15 at 22:51
  • 1
    Your going to want to navigate either Report Manager or the Report Server to download the report. Once you have the .rdl file, simply perform a find for "dataset" and inspect the dataset values. You will see the datasource, parameter and sp. https://technet.microsoft.com/en-us/library/dd255239(v=sql.110).aspx – Steve Mangiameli Dec 10 '15 at 22:58
  • How? Where are they? – B. Clay Shannon-B. Crow Raven Dec 10 '15 at 23:01
  • 1
    You can use BIDS, as referenced in my first comment. For what you want though, just check out the link I posted. Pictures will show you how to download the rdl. Then just perform a "find" for "dataset" – Steve Mangiameli Dec 10 '15 at 23:09
  • With regards to your "Update 2" wanting a reference to the report name, in the footer of every report we have the following expression to show report name and page number : `=Globals!ReportName + ". Page " & Globals!PageNumber & " of " & Globals!TotalPages` – Chris Latta Dec 11 '15 at 00:36
  • @ChrisLätta: I wish we did. I do not have access to the Report Designer/don't have it installed, so I'm pretty much stuck with what I've got. – B. Clay Shannon-B. Crow Raven Dec 11 '15 at 00:38
  • 1
    Yep, doesn't help your current situation but if you're ever editing a report or creating a new one, it's a handy thing to include for the future. – Chris Latta Dec 11 '15 at 00:41

2 Answers2

3

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.

Chris Latta
  • 20,316
  • 4
  • 62
  • 70
  • 1
    Hope you aren't on a production system or you risk crashing the database. Profiler is very heavy with regards to resources. This is like using a 20 pound sledge to drive a finish nail. – Steve Mangiameli Dec 10 '15 at 22:57
  • 1
    I agree it is a blunt instrument but for the OP's problem "*I need to find out which query is being run*" then the few seconds this will take shouldn't be a problem. If it is, then you have bigger problems... – Chris Latta Dec 11 '15 at 00:59
  • Maybe as a last resort. But in a very busy OLTP this is a horrible idea. – Steve Mangiameli Dec 11 '15 at 04:42
3

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/

Steve Mangiameli
  • 688
  • 7
  • 15