Logging query text without Profiler

Question

I'm in the process of porting an old, semi-broken ASP.NET/SQL Server application to PHP/PostgreSQL. Among the torments^H^H^H^H^H^H^H^Hchallenges I'm faced with is that the reporting is done by a series of binary executables, because apparently I've been a bad person and need to be punished. So, I don't have access to the queries in order to port them.

That's fine, I thought. I can run the query profiler and log all SELECT statements that occur during the nightly report generation.

Except it turns out that the original developer shaved the cost down by running SQL Server Workgroup Edition, which doesn't include Profiler.

I was considering using triggers to get these queries, but I think I can't use triggers on SELECT statements. Reverse engineering the queries is possible, but given the complexity of the database (multiple, often recursive, relationships between tables, loads of similarly-named tables where several haven't been updated in years, and so on) I would prefer to have canonical queries to reference.

How should I go about this? Is there any way to get Profiler running in Workgroup Edition? Is there a way to enable query logging without the profiler? I'm not worried about performance because this app doesn't get particularly heavy use and it's running on a machine that's two sizes beefier than the app requires.

• Ali Razeghi - AWS: Have you tried a server side trace or extended events yet?
• sudowned: Extended Events were added in SQL Server 2008, which is newer than I have to work with. I'm intrigued by Server Side Traces, but I don't see an obvious way to log queries from the MSDN doc page. Continuing to read them, though.
• Ali Razeghi - AWS: Oh good call! You're right you're on 2005. Please let me know if you need help with the server side trace.

Answer 1

Several options:

1. You could use SQL Server Profiler against the Workgroup Edition server from a different client tools installation of SQL Server Developer, Standard, Enterprise or Evaluation Edition.

2. Use the semi-documented trace flag 4032, as explained by Tom LaRock.

3. Evaluate third party tools like the free DataWizard SQL Performance Profiler, xSQL Profiler (free for one instance), or ExpressProfiler (BSD Licence).

My preference would probably be option (1), downloading Evaluation Edition if necessary.

• sudowned: Well dang, I didn't know #1 would even work. Gonna try that. :)

Answer 2

If you tell Profiler not to filter out its own queries, you'd probably see it calling these stored procedures:

• sp_trace_create to create a trace
• sp_trace_setevent to add or remove events and columns (can only be called on a stopped trace)
• sp_trace_setfilter to apply a filter to a trace (can only be called on a stopped trace)
• sp_trace_setstatus to start and stop a trace

As described by the documentation, you execute the procedures in this order:

1. Create a trace by using sp_trace_create.
2. Add events with sp_trace_setevent.
3. (Optional) Set a filter with sp_trace_setfilter.
4. Start the trace with sp_trace_setstatus.
5. Stop the trace with sp_trace_setstatus.
6. Close the trace with sp_trace_setstatus.

You will end up with a .trc file that you can then open in SQL Profiler or read with fn_trace_gettable.

Here's an example script to start a trace:

declare @rc int
declare @TraceID int
-- create the trace
exec @rc = sp_trace_create @TraceID output, 0, N'trace-filename'
-- set which events to capture
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
-- start the trace
exec sp_trace_setstatus @TraceID, 1

Answer 3

Use the built-in dynamic management views. AFAIK, they work just fine in Workgroup Edition.

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

https://stackoverflow.com/questions/3579092/tsql-get-last-queries-ran

• sudowned: This is likely my SQL Server naivete, but I'm getting a syntax error even though it looks right to me: Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '.'.