If you've spent much time using SQL Profiler you know that it's an incredibly
useful tool, but can be incredibly frustrating as well. One such instance is
when you set up a trace to log the results to a table and then save it as a
script to use later - it doesn't work! If you're not familiar with this part of
Profiler, here is where you enable the save to table option:
This works fine in Profiler. If you use this method, it's a good idea to log
to a separate server (or even a workstation running Personal edition) so that
the overhead of logging doesn't affect whatever process you're working on.
When you use the File | Script Trace menu selection, this is what you get -
notice the section I've highlighted in blue:
/****************************************************/
/* Created by: SQL Profiler */
/* Date: 12/23/2001 08:38:51 AM */
/****************************************************/
-- 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
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 13, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 16, @on
exec sp_trace_setevent @TraceID, 17, 17, @on
exec sp_trace_setevent @TraceID, 17, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- 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:
go
Is that weird or what? It turns out you can only log the results to a table
from Profiler. If you want to run the profile as a script your only choice is to
log to a file and import afterwards into a table using the system function
fn_trace_gettable, like this:
SELECT * INTO temp_trc FROM ::fn_trace_gettable(c:\my_trace.trc",
default).
Wouldn't you think if Profiler could do it there would be a way for you to do
it without Profiler? Being curious, I decided to profile Profiler to see what
was going on under the hood. To do this, I just removed the 'SQL Profiler'
application name filter and ran a standard trace, then started another instance
of Profiler making no changes to the filter. I then connected using Query
Analyzer and ran a simple select count query. Here are the highlights of what I
captured:
CREATE TABLE [TraceTest] ([RowNumber] int identity PRIMARY KEY,[EventClass] int NULL,[TextData] ntext NULL,[NTUserName] nvarchar(128) NULL,[ClientProcessID] int NULL,[ApplicationName] nvarchar(128) NULL,[LoginName] nvarchar(128) NULL,[SPID] int NULL,[Duration] bigint NULL,[StartTime] datetime NULL,[Reads] bigint NULL,[Writes] bigint NULL,[CPU] int NULL)
select count(*) from sysobjects
exec sp_executesql N'INSERT INTO [TraceTest] ([EventClass],[TextData],[NTUserName],[ClientProcessID],[ApplicationName],[LoginName],[SPID],[Duration],[StartTime],[Reads],[Writes],[CPU]) VALUES ( @P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12)', N'@P1 decimal(10,0),@P2 ntext,@P3 nvarchar(8),@P4 int,@P5 nvarchar(36),@P6 nvarchar(14),@P7 int,@P8 bigint,@P9 datetime,@P10 bigint,@P11 bigint,@P12 int', 12, N'select count(*) from sysobjects
', N'Andy', 2176, N'SQL Query Analyzer', N'EG\Andy', 54, 0, 'Dec 23 2001 8:37AM'
I know that's not the easiest thing to read. Basically Profiler is creating
the table 'TraceTest' for us, then just generating an insert statement each time
it receives a profile event - in this case the 'select count(*) from sysobjects'
I executed in Query Analyzer. This means that as far as I can tell, the ability
to log to a table is part of Profiler and not a built in ability of SQL itself,
a conclusion supported by the comments in the script that says 'client side
table cannot be scripted'!
Does that mean there is NO way to run a trace on the server and have it log
directly to a table? No easy way, that's for sure. I can think of two
possibilities. One is to capture events the same way Profiler does and
essentially replicate it's behavior of creating the table and inserting events
as received. I don't see a Profiler COM object, so doing this may require lower
level programming than I know how to do! The other would be to automate Profiler
to get it to do the work for us, either using it's ability to load a template
using a command line switch (doesn't seem to preserve the log to table settings)
or by sending keystrokes to simulate a user setting up the profile (a bad
hack!).
Logging to a file and then importing later is not a terrible thing, at least
they provided the function to do most of the work for us. You have to use
sp_trace_setstatus to stop the trace first, then you can load it into a table.
So readers, anyone have a better idea? Post your comments in the attached
discussion forum and maybe we'll find a better way to do this!
