Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL or MDX query trace in SSRS - trick of imagination or a real thing?

I sometimes have ghost memories - I remember some things that had never in fact happened. And I was wondering if  my remembering that Reporting Services 2000 had an option in its configuration file that enabled query logging in Reporting Services log files (LogFiles folder) along with the name of the user who executed the statement. I cannot find anything on Google nowadays. It seems that I have lost my ability to correctly state the question to Google to get my answer.

Anyway, I was wondering if that option was ever available in SQL Server Reporting Services 2000?

I tried to see if query logging for each executed report is available in SQL Server 2005, but did not find anything too. There is of course SQL Profiler, but I was looking for something that would put together username, report ID, execution time AND the MDX or SQL query that was executed with the report.

I guess you could do it manually by comparing execution log table in SSRS with SQL Profiler trace data - but if you have a lot concurrent users running the same report - that could become an unpleasant task.

I tried to see if SSRS Web.config options for Trace could help me

<RStrace>

    <add name="FileName" value="ReportServer_" />

    <add name="FileSizeLimitMb" value="32" />

    <add name="KeepFilesForDays" value="14" />

    <add name="Prefix" value="tid, time" />

    <add name="TraceListeners" value="debugwindow, file" />

    <add name="TraceFileMode" value="unique" />

    <add name="Components" value="all,RunningJobs:3,SemanticQueryEngine:4,SemanticModelGenerator:2" />

But it seems that query tracing works only for reports created in Report Builder, and not the old style reports done in Visual Studio.

I am still wondering - was query tracing ever available in SSRS?

Comments

Posted by Anonymous on 31 August 2010

Pingback from  Dew Drop &ndash; August 31, 2010 | Alvin Ashcraft&#039;s Morning Dew

Posted by Anonymous on 31 August 2010

Pingback from  SQL or MDX query trace in SSRS &#8211; trick &#8211; SQL Server Central - sql

Posted by nelia.dragoo on 7 September 2010

This article describes how to monitor reporting services by implementing an execution logging database and reviewing reports that query that database.

msdn.microsoft.com/.../aa964131(SQL.90).aspx

It that what you are seeking?

Posted by aedna on 13 October 2010

2 nelia.dragoo

Thanks, but unfortunately this functionality logs the execution details such as execution time, success or failure, but not the T-SQL or MDX query itself

Leave a Comment

Please register or log in to leave a comment.