Recent Queries in SQL Server 2005

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

SQL Server Profiler was designed to do this.

With it you can:

• capture queries as they are executed on the target instance

• capture other instance and database events like deadlocks, logins, and errors

• filter captured queries by text in the query, login name, database name, and so forth

StackExchange Database Administrators Q#5881, answer score: 3