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 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.

share|improve this question
    
Have you tried a server side trace or extended events yet? –  Ali Razeghi Dec 6 '12 at 21:17
    
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. –  sudowned Dec 6 '12 at 21:20
    
Oh good call! You're right you're on 2005. Please let me know if you need help with the server side trace. –  Ali Razeghi Dec 7 '12 at 0:36
add comment

3 Answers

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 or xSQL Profiler (free for one instance).

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

share|improve this answer
3  
Well dang, I didn't know #1 would even work. Gonna try that. :) –  sudowned Dec 6 '12 at 23:13
add comment

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

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
share|improve this answer
    
+1 Another good option –  Paul White Dec 7 '12 at 14:52
add comment

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

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

share|improve this answer
    
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 '.'. –  sudowned Dec 6 '12 at 21:57
1  
This query parses and runs fine for me on 2005 developer edition, so perhaps it's another workgroup restriction. Not sure. Make sure the database is in 90 compatibility mode though. –  Paul White Dec 6 '12 at 22:22
add comment

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.