SQL Server 2008 - Capturing all SQL Statements Hitting the Server
If your problem with Profiler isn't that you don't want to use it, but that you can't use it, perhaps you could use Profiler for Microsoft SQL Server 2005/2008 Express Edition It's free and open source.
Extended Events in SQL Server 2008. These seem fairly underused. Perhaps due to a lack of UI support but are more flexible than SQL Traces (more events and better filtering possibilities) more light weight (due to better filtering and possibility to drop events rather than block)
Example syntax is below. There are lots more events, actions, predicates and output target possibilities than that though.
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='test_trace')
DROP EVENT SESSION [test_trace] ON SERVER;
CREATE EVENT SESSION [test_trace]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text)
)
,
ADD EVENT sqlserver.sp_statement_completed(
ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text)
)
ADD TARGET package0.asynchronous_file_target
(set filename = 'c:\temp\test_trace.xel' , metadatafile = 'c:\temp\test_trace.xem')
ALTER EVENT SESSION [test_trace] ON SERVER STATE = START
And to review the results
SELECT CONVERT (XML, event_data) AS data
FROM sys.fn_xe_file_target_read_file ('C:\Temp\test_trace*.xel',
'C:\Temp\test_trace*.xem', NULL, NULL)