Scripting Trace's for SQL 2005

I wrote this with the assumption that you will be running traces around job steps, but you can adapt this for other situations.I used the default traces for both SQL and AS traces, but removed most of the Events from both the trace definitions in this write-up, just for brevity, you can generate the full definitions from Profiler by following the steps below. I have sections for SQL Server and Analysis Services.

SQL Trace

1. Create the Trace you want in SQL Profiler, on a SQL connection, setting a Trace Name (I used 'OLAP Error SQL Data'), Save To File with a file name and a file size large enough to hold the data in a single file (I used 32MB), and the Events / Data fields you want to trace. Run the Trace.

2. Stop the Trace - we don't actually want to collect anything at this point.

3. Export the trace definition - select "FileExportScript Trace DefintionFor SQL 2005...". Export the definition to a .SQL file of your choice.

4. Close Profiler, we're done with it at this point.In the SQL job with the step you want to trace, you're going to add a couple of steps before the step you want to trace...

5. Add the following code to a T-SQL job step, named 'Delete SQL Trace File', before the step you want to trace (this step is just to make sure there isn't a file from a previous run, just in case):
DECLARE @OS_Cmd VARCHAR(1024)SELECT @OS_Cmd = 'DEL /Q /F "C:\Trace Files\OLAP Error SQL Data.trc"'EXECUTE master.dbo.xp_cmdshell @OS_CmdNOTE - you will get an error code 12 from the sp_trace_create call in the next step if the output file already exists.

6. Add the code from the SQL file you just created to a T-SQL job step, named 'Start SQL Trace', immediately after the 'Delete SQL Trace File' step. Replace the literal 'InsertFileNameHere' with the destination you want for the trace file (don't add .trc to the end of the file name - it will automatically get added for you). It should look like this:

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 32-- Please replace the text InsertFileNameHere, with an appropriate
--filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
--will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'C:\Trace Files\OLAP Error SQL Data', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 1745445d-46a5-4050-9922-16caf3851690'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error: select ErrorCode=@rc
finish:
Now you're going to add a couple of steps after the step you want to trace...

7. You need to stop the trace. Add this code to a T-SQL step named 'Stop SQL Trace', note that the file name in the WHERE clause must match the file name you specified above in the 'Start SQL Trace' step, with the '.trc. added to the end:
DECLARE @TraceID INT
SELECT @TraceID = TraceID FROM :: fn_trace_getinfo(0)WHERE CAST([value] AS VARCHAR(256)) = 'C:\Trace Files\OLAP Error SQL Data.trc'
IF @TraceID IS NOT NULL
BEGIN
PRINT 'Closing Trace ID ' + CAST (@TraceID AS VARCHAR(5))
EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2
END