How to get the SQL for a Report Builder report



There are two ways to get the generated SQL for a Report Builder report (or for a model-based query in any RS report): 1) use SQL Profiler to capture the incoming SQL commands, or 2) enable query logging in the report server.


To enable query logging on the report server (option #2), make the following change to the web.cofig file in your ReportServer install directory:


Before:
<?xml version=”1.0″ encoding=”utf-8″ ?>
<configuration>
  …
  <RStrace>
    …
    <add name=”Components” value=”all,RunningJobs:3,SemanticQueryEngine:2,SemanticModelGenerator:2″ />
  </RStrace>


After:
<?xml version=”1.0″ encoding=”utf-8″ ?>
<configuration>
  …
  <RStrace>
    …
    <add name=”Components” value=”all,RunningJobs:3,SemanticQueryEngine:4,SemanticModelGenerator:2″ />
  </RStrace>


You can then run a report and you will find the corresponding SQL statement in the log file at:


…\Reporting Services\LogFiles\ReportServer_<datetime>.log

Comments (5)

  1. bwalker says:

    Bob, I spoke with you several months ago with regards to passing parameters to a report model in a URL.  We are back on the project again and I was wondering if this is a possible solution.

    We are an asp application with multiple facilities and users accessing a single database.  The primary indicator for each user is "docid" which restricts the data the user can access in the database.

    We want to build a report model that when opened in Report Builder only pulls data based on the user’s "docid".

    Can what is discussed in this post help with that.

    Thanks

  2. PhilNicholas says:

    The SQL is very complex for even quite simple reports, if someone I was working with wrote a report with the SQL generated I wouldnt be happy. Do you have plans to optimise this?

  3. Alvin777 says:

    I enabled that setting and still don’t get queries in the log. Where should I expect them? And why don’t I get them?

    Here is the log:

    <Header>

     <Product>Microsoft SQL Server Reporting Services Version 9.00.2047.00</Product>

     <Locale>en-US</Locale>

     <TimeZone>Russian Standard Time</TimeZone>

     <Path>C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServer__12_22_2006_07_02_28.log</Path>

     <SystemName>GOLF2</SystemName>

     <OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName>

     <OSVersion>5.2.3790.65536</OSVersion>

    </Header>

    w3wp!webserver!5!22.12.2006-07:02:29:: i INFO: Reporting Web Server started

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing ConnectionType to ‘0’  as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing IsSchedulingService to ‘True’  as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing IsNotificationService to ‘True’  as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing IsEventService to ‘True’  as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing PollingInterval to ’10’ second(s) as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing WindowsServiceUseFileShareStorage to ‘False’  as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing MemoryLimit to ’60’ percent as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing RecycleTime to ‘720’ minute(s) as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing MaximumMemoryLimit to ’80’ percent as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing MaxAppDomainUnloadTime to ’30’ minute(s) as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing MaxQueueThreads to ‘0’ thread(s) as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing IsWebServiceEnabled to ‘True’  as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing MaxActiveReqForOneUser to ’20’ requests(s) as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing MaxScheduleWait to ‘5’ second(s) as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing DatabaseQueryTimeout to ‘120’ second(s) as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing ProcessRecycleOptions to ‘0’  as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing RunningRequestsScavengerCycle to ’60’ second(s) as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing RunningRequestsDbCycle to ’60’ second(s) as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing RunningRequestsAge to ’30’ second(s) as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing CleanupCycleMinutes to ’10’ minute(s) as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of ‘120’ minutes since midnight because it was not specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing WatsonFlags to ‘1064’  as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing WatsonDumpOnExceptions to ‘Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException’  as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to ‘System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException’  as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing SecureConnectionLevel to ‘0’  as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing DisplayErrorLink to ‘True’  as specified in Configuration file.

    w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing WebServiceUseFileShareStorage to ‘False’  as specified in Configuration file.

    w3wp!resourceutilities!5!22.12.2006-07:02:29:: i INFO: Reporting Services starting SKU: Enterprise

    w3wp!resourceutilities!5!22.12.2006-07:02:29:: i INFO: Evaluation copy: 0 days left

    w3wp!runningjobs!5!22.12.2006-07:02:29:: i INFO: Database Cleanup (Web Service) timer enabled: Next Event: 600 seconds.  Cycle: 600 seconds

    w3wp!runningjobs!5!22.12.2006-07:02:29:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds.  Cycle: 60 seconds

    w3wp!runningjobs!5!22.12.2006-07:02:29:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds.  Cycle: 60 seconds

    w3wp!runningjobs!5!22.12.2006-07:02:29:: i INFO: Memory stats update timer enabled: Next Event: 60 seconds.  Cycle: 60 seconds

    w3wp!library!5!12/22/2006-07:02:31:: i INFO: Catalog SQL Server Edition = Enterprise

    w3wp!library!5!12/22/2006-07:10:23:: i INFO: Call to GetPermissions:/SharePoint reports/Users/User activity

    w3wp!library!5!12/22/2006-07:10:24:: i INFO: Call to GetSystemPermissions

    w3wp!library!5!12/22/2006-07:10:28:: i INFO: Call to GetPermissions:/SharePoint reports/Users/User activity

    w3wp!library!5!12/22/2006-07:10:28:: i INFO: Call to GetSystemPermissions

    w3wp!library!1!12/22/2006-07:10:31:: i INFO: Call to GetPermissions:/SharePoint reports/Users/User activity

    w3wp!library!1!12/22/2006-07:10:31:: i INFO: Call to GetSystemPermissions

    w3wp!library!1!12/22/2006-07:10:33:: i INFO: Call to GetPermissions:/SharePoint reports/Users/User activity

    w3wp!library!1!12/22/2006-07:10:33:: i INFO: Call to GetSystemPermissions

    w3wp!library!1!12/22/2006-07:10:35:: i INFO: Call to GetPermissions:/SharePoint reports/Users/User activity

    w3wp!library!1!12/22/2006-07:10:35:: i INFO: Call to GetSystemPermissions

    w3wp!library!5!12/22/2006-07:10:38:: i INFO: Call to GetPermissions:/SharePoint reports/Users/User activity

    w3wp!library!5!12/22/2006-07:10:39:: i INFO: Call to GetSystemPermissions

  4. Deveshd says:

    Hi bwalker

    I am cming very late on this page.

    I am having the same problem as you do, did you get a chance how to handle it.

    regards

    DD