How to Genrate trace directly in to table using t-sql script

[goher2000]

hi there, I have a question for and need help. Can we genrate trace output directly into table using t-sql? if yes, how?

[skhanal]

Yes.

Use SQL Profiler to define all the settings including table to use for trace data. Then on File menu select Script trace to save the .sql file, this is what you are looking for.

[goher2000]

Thankyou for your help, but this is what I am getting.. "Writing to a table is not supported through the SP's"


/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 05/14/2003 02:25:18 PM */
/************************************************** **/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- 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'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Writing to a table is not supported through the SP's

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
.
.
.
.

[skhanal]

I did not know that.

You can import the .trc file data into a table using BULK INSERT command.

If you know before hand how long you are going to run the trace then you can use WAITFOR to wait for that duration, then stop the trace and run BULK INSERT in the same script.