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.
Best Answer
Several options:
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.
Use the semi-documented trace flag 4032, as explained by Tom LaRock.
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.