Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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
share|improve this answer
    
Perfect, catches a bit more info than I need but I'm tinkering with the filters. –  Ben Brocka Sep 16 '11 at 21:14
1  
@SirTapTap - You can select the combination of events, columns, and column filters that gives you just what you need. When you've done that, be sure to save it as a template so you can reuse that configuration in future Profiler sessions. –  Nick Chammas Sep 16 '11 at 21:19
    
thanks, messing with that now –  Ben Brocka Sep 16 '11 at 21:51

If you get the SPID that is running the queries, this will give you the last statement run:

DECLARE @sqltext VARBINARY(128)

SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 85 --replace this with your SPID

SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
share|improve this answer
    
Helpful but is there any way to get more than one statement from this? Unfortunately the operation I'm analyzing is a large series. –  Ben Brocka Sep 16 '11 at 21:15
    
Nope, as you noted above the Profiler is better for that sort of thing. We found the above query useful because we had one column that was being changed and implemented a temporary trigger to write the results of that query to a table because profiling all of the possible databases (and not knowing when or where the data could be changed) wasn't feasible. –  Wil Sep 16 '11 at 21:23
    
You could configure Profiler via column filters to capture just the queries that include the column name in question, just on the databases in question. –  Nick Chammas Sep 16 '11 at 21:29
    
@Nick, granted yes. 150+ databases = no bueno though :) –  Wil Sep 16 '11 at 21:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.