Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Retreiving SQL from Ad-hoc SSRS Report

Posted on 2009-05-14
4
Medium Priority
?
437 Views
Last Modified: 2012-08-14
I have a user that created an ad-hoc report in SQL Server 2005.  When I log into the SQL Server DB and export the report to an RDL file for editing, the DataSource is in XML (NOT SQL)and therefore when I load the RDL file in a Visual Studio Solution, I cannot click the Data tab and see the SQL for the report.  Nor can I hit the preview button.

If I try and export the DataModel or Dataset from the DB and add it to the solution it does not add to the datasource section.  It adds it under the report in the solution explorer.

I need to somehow get the exact SQL that this report has internally from the RDL which runs against a datamodel in the DB.

Any ideas?
0
Comment
Question by:ralphp135
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 9

Expert Comment

by:cdaly33
ID: 24386094
If this is a one-time thing try SQL Profiler to capture the SQL query.
0
 

Author Comment

by:ralphp135
ID: 24387184
What i am looking for is 1) to get the SQL from the RDL and 2) to be able to take these adhoc reports and migrate them into a full development environment in files such as RDL that I can put in Visual Source Safe.
0
 
LVL 1

Accepted Solution

by:
billinb earned 2000 total points
ID: 24401264
9:10 PM 5/15/2009

The SQL profiler solution actually is the best way to go here, I'll explain.
If it were possible to glean any useful information from the model, You would need to create a Report Model project rather than a Report Server project, then observe the model in VS.

But you wouldn't see any additional information regards the SQL.  A model establishes table/entity relationships, measures, attributes, etc for analysis type reporting and drag and drop functionality.

Note that the connections for the existing models are probably in the root folder, and the models are probably in a 'Models' folder on RM.  Click the show details to view all objects.

So back to profiler, I'll make it easy and it'll be painless.

Assuming you can log to the SQL server that SSRS management resides on:
Tools/Profiler;  File/New Trace;
Login;
Use Standard Default on the General tab, then click on Events Selection.
Click Column Filters;
Select Login Name, then type like 'ReportingServices'.
Click Run;
Pause and Clear the trace;
Run the report in Manager;
Alt Tab back to profiler quickly, and Stop the Trace as soon as the report finishes;
Ctrl-F Find, to find an Exec or some other object name in order to find the SQL that was generated when the report was run.  Replace Scalars with formulas in your SQL (the model will generate literals).

Paste that SQL into a new Report Server's RDL report's dataset SQL and create a new report.  Move the old report and deploy the new rdl report in its place, or deploy the new report in a different folder, or create a linked report to the new one.

This should save you time and make it easier to gather any SQL you might need to consolidate.  You can always migrate toward analysis (model) reports later.

Hope this helped.

Bill
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question