Debugging SQL statement

I am working in SQL Server Reporting Services, and I have an error using a dynamic parameter. The error surfaces during runtime. I believe I could fix the parameter if I knew exactly what the resulting SQL statement looks like. So the question is: how do I view the actual SQL statement that is being executed in a report? I tried SQL Profiler, which shows how my parameter drop-down lists are being populated, but it does not show the main dataset SQL statement.

Alternatively, are there any examples of using a dynamic parameter that is a "datetime" data type?

Any help would be appreciated. Thanks.

Hello,

When are you getting the error, after setting your parameters when you click "View Report"?

You should be capturing the 'Exception' and 'SQL:BatchStarting' events in your trace. This will first whenever the report is started (regardless of error) and any error from the SQL statement.

Of course, if you have the RDL file, you can always look in there for the SQL command under the <CommandText> tag.

Hope this helps.

Jarret

|||

Thanks for responding.

Yes, I am getting the error after I click "View Report". I have the RDL file, but next to the <CommandText> tag, I see the same statement construct that I used in the Dataset (i.e. I see the IIf(parameter!... structure instead of an actual SQL statement) . I'll check out the trace again, but I'm certain that the only SQL statements that were in there pertain to setting up the parameter lists (e.g. "select salesrep from sourcetable").

Thanks again.

|||

Were you able to set up the trace and see the exception and the main dataset's SQL statement? Do you get the error when you're in BIDS using the preview tab? If so, you should be able to see the error (it may be in the output window, Ctrl+Alt+O).

If you have multiple dataset's there should be a <CommandText> tag for each one in your RDL file. Inside of each of these should be the SQL query. Are you saying you have references to Parameters!... in your dataset? Your dataset command should only contain a valid SQL statement, as this is what is sent to the DB server to be executed.

Go to the data tab in BIDS and select your main dataset from the 'Dataset:' dropdown. It should load that command (from the RDL file), then hit the red Exclamation button to run the query. Do you get an error?

Jarret