question

Tatyana avatar image
Tatyana asked

SSRS and Profiler

Hi, I'd like to, actually, repeat a question asked here before, since I don't think it got answered and I have the same problem! So, the old question was: "...I thought SQL Profiler could show me the SQL that is being executed [in a SSRS report] ... I have three datasets: the first is a straight SQL, the second is a SQL statement with a parameter ... and the third is a SQL statement with a multi-value parameter ... The only SQL statement that I ever see in SQL Profiler is the first one. Does anyone have any idea as to why the SQL statements [for report datasets] with parameters don’t show in Profiler? I've been using primarily SQL:StmtCompleted..." This is exactly what I've stumbled over now. Any parameterized datasets that run from inner report queries, well, they actually do show in Profiler, but in the TextDate field they all have the same value: SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation'), COLLATIONPROPERTY(CONVERT(char, DATABASEPROPERTYEX(DB_NAME(), 'collation')), 'LCID')... Why so? And, is there ANY smart way (via Profiler or otherwise) to monitor report's dataset queries performance and to tune them?
ssrsprofiler
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Tim avatar image
Tim answered
You already stated you are capturing SP:StmtCompleted, are you also capturing RPC:Starting or RPC:Completed?
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Tatyana, glad I could help. This site has helped me so much, it is an honor to help others.
3 Likes 3 ·
That was quick! :) I did not even try the SP group - I was capturing SQL:StmtCompleted and others from SQL group... but you are absolutely correct: "RPC:Starting" and "RPC:Completed" populate the TextData field with the complete SQL statement executed in a report. Thank you very much!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.