Monday, August 30, 2010

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?