question

erlokeshsharma08 avatar image
erlokeshsharma08 asked

Queries for child pararameters not visible in profiler

Hi Folks Good Morning/Evening I have a ssrs report that is built on sql server 2005. It has some input cascading parameters meaning a parent parameter and 2 child parameters. For example when you select USA as country (parent parameter) then some states and sub states populate into child parameters - State and SubState. I had some problems with the queries for child parameters, so ran a trace inside sql server and could only see the query for a parent parameter and no query for child parameter . I was running the report in report manager. Is this behavior normal?
profiler
5 comments
10 |1200

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

If you ran the report from parameter selection through to completion, I would expect to see both sets of parameter queries
0 Likes 0 ·
Why would we have to run the report to completion to get the sub queries (for child parameter) to be recorded in trace, because when I select the parent parameter, the child parameters do get their values populated based on parent parameter's value. This means some sql query must have had run at the backend.
0 Likes 0 ·
Assuming each one had to make a call back to the server, no, it's not normal, I would expect to see the query to the server, if indeed, each one makes a call to the server.
0 Likes 0 ·
Can you please try to simulate it Grant. That would be helpful
0 Likes 0 ·
Sorry. I'm not an SSRS expert. I don't even have it installed on my machine. But assuming it's making a query to the database, you should be able to capture it in either extended events or trace events.
0 Likes 0 ·

1 Answer

·
erlokeshsharma08 avatar image
erlokeshsharma08 answered
Tried to simulate this evening and to my surprise it is showing in trace results in the form of dynamic query i.e. exec sp_executesql. event class - rpc completed application name - .net sqlclient data provider Sorry for the confusions (may be a security limitation) and thanks for suggestions. How should I close this question?
2 comments
10 |1200

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

Mark this as the answer. You figured it out on your own.
0 Likes 0 ·
Must say the points system really makes sense here.
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.