November 20, 2009 at 4:44 am
Hello
I have an application that uses embedded sql and I don't have the source code. This application runs queries against a certain SQL2005 DB which I have access in order to display reports. I would like to somehow capture the sql text for each of the reports so that I can learn how the query is structured. How can I do that?
Thanks a lot.
November 20, 2009 at 5:51 am
Use the SQL server Profiler. In SSMS -> Tools menu -> SQL Server Profiler.
-Vikas Bindra
November 20, 2009 at 4:53 pm
Another option is if the application is using SSRS to display the reports, then you should be able to see the query used in the rdl file. There will be a section (or sections depending on how many queries are used to make up the report) called commandtext that has the query statement(s) used to make the report.
Joie Andrew
"Since 1982"
November 23, 2009 at 8:07 am
What you can do does depend on your permissions as well. In addition to Profiler you can also do something like this immediately after running the reports:
SELECT
DEQS.statement_start_offset,
DEQS.statement_end_offset,
DEQS.creation_time,
DEQS.last_execution_time,
DEST.dbid,
DEST.objectid,
DEST.number,
DEST.encrypted,
DEST.[text]
FROM
sys.dm_exec_query_stats AS DEQS CROSS APPLY
sys.dm_exec_sql_text(DEQS.sql_handle) AS DEST
WHERE
last_execution_time >= 'Your start execution time'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply