I'm using a PHP web application to interact with a database and need to catch an erroneous query. It's dynamically generated SQL and I don't know 100% of the code nor did I write it so I need to see what it's doing.
Using SQL Server Management Studio 2008 I've found it amazingly hard to find the last n executed queries on the database. I've searched up a storm but all solutions I've seen require "sql_handle" which is a feature not availible in my database's compatibility level, and to boot all solutions I've seen don't appear to catch dynamic queries, which this web application uses.
I was using this query:
SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
execution_count,s2.objectid,
(SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
ORDER BY last_execution_time DESC
But it unacceptably requires changing sp_dbcmptlevel in SQL Server 2005 (the search requires comptlevel at 90, I need it at 80), and in a recent case doesn't even return any dynamic queries run on the database; it's only returning the actions of stored procedures.
A similar solution with the same problems of compatability and incomplete results is:
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
This one returns a very small and very odd subset of queries, including the most recent query (usually itself) and a bunch of create procedure
statements.
Is there any feature of SSMS or system database query that will allow me to view or capture recent queries? If I have to capture and save the query strings somehow that's acceptable as this is only in a test system.
Best Answer
SQL Server Profiler was designed to do this.
With it you can: