How to set sql profiler to profile SQL 2005 reporting services

Question

I'm trying to profile SQL reporting services, used from ASP.NET application. In SQL profiler all the SQL run by ASP.NET shows up. It looks like the reporting SQL (from the RDL) doesn't show. Is there some setting or filter I'm missing?

Answer 1

When you get that big ball of mess, you can search it. I would search for an sp or sql statement that you know could only be used by SSRS. (If this doesn't exist, then force something in there just for testing purposes). Look at all the columns. There may be a column that jumps out at you as unique to reporting services that you could use as a filter.

Answer 2

Application name column = Reporting Services (or similar) usually.

You may need to trace SQL batch complete and RPC call complete I've been bitten with this before...

Answer 3

So there's a few ways I profile that could help you.

1. Add the column named "HostName" and you'll get the server name appearing as the computer running the report.
2. Add a reporting login name to the database and use that name on the reporting service's Shared Data Source, and then filter by LoginName.
3. If you add a comment to the report, then you will see that comment and the sql of the report appear in the Data window.

For the third one, what I mean is do this:

---

-- Get Products Report

select productid, productname from products

---

And the comment line will appear in the window along with the SQL, which makes it very easy to track to a report when you're noticing one of them is causing issues, further on down the track.

Hope that helps.