here's the proc I use to create the trace I use; note that it creates a view at the end so I can also see the results easily.

this runs till you turn it off;

hope this helps:

CREATE procedure sp_AddMyTrace

AS

BEGIN

SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRAN

declare @sql varchar(1000)

declare @path nvarchar(256)

declare @traceidout int

declare @maxfilesize bigint

declare @maxRolloverFiles int

declare @on bit

set @on = 1

set @maxRolloverFiles = 2

set @maxfilesize = 50

--we want the current trace folder

--ie c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1

SET @path = @path + N'MyDMLtrace' --system appends .trc automatically for the filename

--create the trace

exec sp_trace_create @traceidout output, @maxRolloverFiles, @path, @maxfilesize, NULL

--for the Event Every SQL statement completed, capture all 64 columns of accessible data

exec sp_trace_setevent @traceidout, 12, 1, @on --SQL:BatchCompleted,TextData

exec sp_trace_setevent @traceidout, 12, 2, @on --SQL:BatchCompleted,BinaryData

exec sp_trace_setevent @traceidout, 12, 3, @on --SQL:BatchCompleted,DatabaseID

exec sp_trace_setevent @traceidout, 12, 4, @on --SQL:BatchCompleted,TransactionID

exec sp_trace_setevent @traceidout, 12, 5, @on --SQL:BatchCompleted,LineNumber

exec sp_trace_setevent @traceidout, 12, 6, @on --SQL:BatchCompleted,NTUserName

exec sp_trace_setevent @traceidout, 12, 7, @on --SQL:BatchCompleted,NTDomainName

exec sp_trace_setevent @traceidout, 12, 8, @on --SQL:BatchCompleted,HostName

exec sp_trace_setevent @traceidout, 12, 9, @on --SQL:BatchCompleted, ClientProcessID

exec sp_trace_setevent @traceidout, 12,10, @on --SQL:BatchCompleted,ApplicationName

exec sp_trace_setevent @traceidout, 12,11, @on --SQL:BatchCompleted,LoginName

exec sp_trace_setevent @traceidout, 12,12, @on --SQL:BatchCompleted,SPID

exec sp_trace_setevent @traceidout, 12,13, @on --SQL:BatchCompleted,Duration

exec sp_trace_setevent @traceidout, 12,14, @on --SQL:BatchCompleted,StartTime

exec sp_trace_setevent @traceidout, 12,15, @on --SQL:BatchCompleted,EndTime

exec sp_trace_setevent @traceidout, 12,16, @on --SQL:BatchCompleted,Reads

exec sp_trace_setevent @traceidout, 12,17, @on --SQL:BatchCompleted,Writes

exec sp_trace_setevent @traceidout, 12,18, @on --SQL:BatchCompleted,CPU

exec sp_trace_setevent @traceidout, 12,19, @on --SQL:BatchCompleted,Permissions

exec sp_trace_setevent @traceidout, 12,20, @on --SQL:BatchCompleted,Severity

exec sp_trace_setevent @traceidout, 12,21, @on --SQL:BatchCompleted,EventSubClass

exec sp_trace_setevent @traceidout, 12,22, @on --SQL:BatchCompleted,ObjectID

exec sp_trace_setevent @traceidout, 12,23, @on --SQL:BatchCompleted,Success

exec sp_trace_setevent @traceidout, 12,24, @on --SQL:BatchCompleted,IndexID

exec sp_trace_setevent @traceidout, 12,25, @on --SQL:BatchCompleted,IntegerData

exec sp_trace_setevent @traceidout, 12,26, @on --SQL:BatchCompleted,ServerName

exec sp_trace_setevent @traceidout, 12,27, @on --SQL:BatchCompleted,EventClass

exec sp_trace_setevent @traceidout, 12,28, @on --SQL:BatchCompleted,ObjectType

exec sp_trace_setevent @traceidout, 12,29, @on --SQL:BatchCompleted,NestLevel

exec sp_trace_setevent @traceidout, 12,30, @on --SQL:BatchCompleted,State

exec sp_trace_setevent @traceidout, 12,31, @on --SQL:BatchCompleted,Error

exec sp_trace_setevent @traceidout, 12,32, @on --SQL:BatchCompleted,Mode

exec sp_trace_setevent @traceidout, 12,33, @on --SQL:BatchCompleted,Handle

exec sp_trace_setevent @traceidout, 12,34, @on --SQL:BatchCompleted,ObjectName

exec sp_trace_setevent @traceidout, 12,35, @on --SQL:BatchCompleted,DatabaseName

exec sp_trace_setevent @traceidout, 12,36, @on --SQL:BatchCompleted,FileName

exec sp_trace_setevent @traceidout, 12,37, @on --SQL:BatchCompleted,OwnerName

exec sp_trace_setevent @traceidout, 12,38, @on --SQL:BatchCompleted,RoleName

exec sp_trace_setevent @traceidout, 12,39, @on --SQL:BatchCompleted,TargetUserName

exec sp_trace_setevent @traceidout, 12,40, @on --SQL:BatchCompleted,DBUserName

exec sp_trace_setevent @traceidout, 12,41, @on --SQL:BatchCompleted,LoginSid

exec sp_trace_setevent @traceidout, 12,42, @on --SQL:BatchCompleted,TargetLoginName

exec sp_trace_setevent @traceidout, 12,43, @on --SQL:BatchCompleted,TargetLoginSid

exec sp_trace_setevent @traceidout, 12,44, @on --SQL:BatchCompleted,ColumnPermissions

exec sp_trace_setevent @traceidout, 12,45, @on --SQL:BatchCompleted,LinkedServerName

exec sp_trace_setevent @traceidout, 12,46, @on --SQL:BatchCompleted,ProviderName

exec sp_trace_setevent @traceidout, 12,47, @on --SQL:BatchCompleted,MethodName

exec sp_trace_setevent @traceidout, 12,48, @on --SQL:BatchCompleted,RowCounts

exec sp_trace_setevent @traceidout, 12,49, @on --SQL:BatchCompleted,RequestID

exec sp_trace_setevent @traceidout, 12,50, @on --SQL:BatchCompleted,XactSequence

exec sp_trace_setevent @traceidout, 12,51, @on --SQL:BatchCompleted,EventSequence

exec sp_trace_setevent @traceidout, 12,52, @on --SQL:BatchCompleted,BigintData1

exec sp_trace_setevent @traceidout, 12,53, @on --SQL:BatchCompleted,BigintData2

exec sp_trace_setevent @traceidout, 12,54, @on --SQL:BatchCompleted,GUID

exec sp_trace_setevent @traceidout, 12,55, @on --SQL:BatchCompleted,IntegerData2

exec sp_trace_setevent @traceidout, 12,56, @on --SQL:BatchCompleted,ObjectID2

exec sp_trace_setevent @traceidout, 12,57, @on --SQL:BatchCompleted,Type

exec sp_trace_setevent @traceidout, 12,58, @on --SQL:BatchCompleted,OwnerID

exec sp_trace_setevent @traceidout, 12,59, @on --SQL:BatchCompleted,ParentName

exec sp_trace_setevent @traceidout, 12,60, @on --SQL:BatchCompleted,IsSystem

exec sp_trace_setevent @traceidout, 12,61, @on --SQL:BatchCompleted,Offset

exec sp_trace_setevent @traceidout, 12,62, @on --SQL:BatchCompleted,SourceDatabaseID

exec sp_trace_setevent @traceidout, 12,63, @on --SQL:BatchCompleted,SqlHandle

exec sp_trace_setevent @traceidout, 12,64, @on --SQL:BatchCompleted,SessionLoginName

--adding RPC calls: When an application executes a stored procedure, only a simple, small RPC (remote procedure call) is made from the client to SQL Server.

exec sp_trace_setevent @traceidout, 10, 1, @on --RPC:Completed,TextData

exec sp_trace_setevent @traceidout, 10, 2, @on --RPC:Completed,BinaryData

exec sp_trace_setevent @traceidout, 10, 3, @on --RPC:Completed,DatabaseID

exec sp_trace_setevent @traceidout, 10, 4, @on --RPC:Completed,TransactionID

exec sp_trace_setevent @traceidout, 10, 5, @on --RPC:Completed,LineNumber

exec sp_trace_setevent @traceidout, 10, 6, @on --RPC:Completed,NTUserName

exec sp_trace_setevent @traceidout, 10, 7, @on --RPC:Completed,NTDomainName

exec sp_trace_setevent @traceidout, 10, 8, @on --RPC:Completed,HostName

exec sp_trace_setevent @traceidout, 10, 9, @on --RPC:Completed, ClientProcessID

exec sp_trace_setevent @traceidout, 10,10, @on --RPC:Completed,ApplicationName

exec sp_trace_setevent @traceidout, 10,11, @on --RPC:Completed,LoginName

exec sp_trace_setevent @traceidout, 10,12, @on --RPC:Completed,SPID

exec sp_trace_setevent @traceidout, 10,13, @on --RPC:Completed,Duration

exec sp_trace_setevent @traceidout, 10,14, @on --RPC:Completed,StartTime

exec sp_trace_setevent @traceidout, 10,15, @on --RPC:Completed,EndTime

exec sp_trace_setevent @traceidout, 10,16, @on --RPC:Completed,Reads

exec sp_trace_setevent @traceidout, 10,17, @on --RPC:Completed,Writes

exec sp_trace_setevent @traceidout, 10,18, @on --RPC:Completed,CPU

exec sp_trace_setevent @traceidout, 10,19, @on --RPC:Completed,Permissions

exec sp_trace_setevent @traceidout, 10,20, @on --RPC:Completed,Severity

exec sp_trace_setevent @traceidout, 10,21, @on --RPC:Completed,EventSubClass

exec sp_trace_setevent @traceidout, 10,22, @on --RPC:Completed,ObjectID

exec sp_trace_setevent @traceidout, 10,23, @on --RPC:Completed,Success

exec sp_trace_setevent @traceidout, 10,24, @on --RPC:Completed,IndexID

exec sp_trace_setevent @traceidout, 10,25, @on --RPC:Completed,IntegerData

exec sp_trace_setevent @traceidout, 10,26, @on --RPC:Completed,ServerName

exec sp_trace_setevent @traceidout, 10,27, @on --RPC:Completed,EventClass

exec sp_trace_setevent @traceidout, 10,28, @on --RPC:Completed,ObjectType

exec sp_trace_setevent @traceidout, 10,29, @on --RPC:Completed,NestLevel

exec sp_trace_setevent @traceidout, 10,30, @on --RPC:Completed,State

exec sp_trace_setevent @traceidout, 10,31, @on --RPC:Completed,Error

exec sp_trace_setevent @traceidout, 10,32, @on --RPC:Completed,Mode

exec sp_trace_setevent @traceidout, 10,33, @on --RPC:Completed,Handle

exec sp_trace_setevent @traceidout, 10,34, @on --RPC:Completed,ObjectName

exec sp_trace_setevent @traceidout, 10,35, @on --RPC:Completed,DatabaseName

exec sp_trace_setevent @traceidout, 10,36, @on --RPC:Completed,FileName

exec sp_trace_setevent @traceidout, 10,37, @on --RPC:Completed,OwnerName

exec sp_trace_setevent @traceidout, 10,38, @on --RPC:Completed,RoleName

exec sp_trace_setevent @traceidout, 10,39, @on --RPC:Completed,TargetUserName

exec sp_trace_setevent @traceidout, 10,40, @on --RPC:Completed,DBUserName

exec sp_trace_setevent @traceidout, 10,41, @on --RPC:Completed,LoginSid

exec sp_trace_setevent @traceidout, 10,42, @on --RPC:Completed,TargetLoginName

exec sp_trace_setevent @traceidout, 10,43, @on --RPC:Completed,TargetLoginSid

exec sp_trace_setevent @traceidout, 10,44, @on --RPC:Completed,ColumnPermissions

exec sp_trace_setevent @traceidout, 10,45, @on --RPC:Completed,LinkedServerName

exec sp_trace_setevent @traceidout, 10,46, @on --RPC:Completed,ProviderName

exec sp_trace_setevent @traceidout, 10,47, @on --RPC:Completed,MethodName

exec sp_trace_setevent @traceidout, 10,48, @on --RPC:Completed,RowCounts

exec sp_trace_setevent @traceidout, 10,49, @on --RPC:Completed,RequestID

exec sp_trace_setevent @traceidout, 10,50, @on --RPC:Completed,XactSequence

exec sp_trace_setevent @traceidout, 10,51, @on --RPC:Completed,EventSequence

exec sp_trace_setevent @traceidout, 10,52, @on --RPC:Completed,BigintData1

exec sp_trace_setevent @traceidout, 10,53, @on --RPC:Completed,BigintData2

exec sp_trace_setevent @traceidout, 10,54, @on --RPC:Completed,GUID

exec sp_trace_setevent @traceidout, 10,55, @on --RPC:Completed,IntegerData2

exec sp_trace_setevent @traceidout, 10,56, @on --RPC:Completed,ObjectID2

exec sp_trace_setevent @traceidout, 10,57, @on --RPC:Completed,Type

exec sp_trace_setevent @traceidout, 10,58, @on --RPC:Completed,OwnerID

exec sp_trace_setevent @traceidout, 10,59, @on --RPC:Completed,ParentName

exec sp_trace_setevent @traceidout, 10,60, @on --RPC:Completed,IsSystem

exec sp_trace_setevent @traceidout, 10,61, @on --RPC:Completed,Offset

exec sp_trace_setevent @traceidout, 10,62, @on --RPC:Completed,SourceDatabaseID

exec sp_trace_setevent @traceidout, 10,63, @on --RPC:Completed,SqlHandle

exec sp_trace_setevent @traceidout, 10,64, @on --RPC:Completed,SessionLoginName

--turn on the trace

exec sp_trace_setstatus @traceidout, 1 ---start trace

--exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it

--exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it

IF EXISTS(select * from master.dbo.sysobjects where xtype='V' and name='sp_DMLTrace')

BEGIN

SET @sql = 'ALTER VIEW sp_DMLTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path +'.trc'', default)'

exec(@sql)

END

ELSE

BEGIN

SET @sql = 'CREATE VIEW sp_DMLTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path + '.trc'', default)'

exec(@sql)

END

COMMIT TRAN

END