Can Python Parse an MS SQL Trace?

[Tim Golden]

kyosohma at gmail.com wrote:
> On May 7, 8:34 am, Tim Golden <m... at timgolden.me.uk> wrote:
>> kyoso... at gmail.com wrote:
>>> Can Python parse a trace file created with MS SQL's profiler? There
>>> are a few thousand lines in the trace file and I need to find the
>>> insert statements and the stored procedures. Unfortunately, I am not
>>> an SQL guru and was hoping Python could help.
>>> Mike
>> Mike,
>>
>> Can I suggest that, since the answer is more to
>> do with parsing and less to do with MSSQL (which
>> simply generated the output) that you post an example
>> of a trace file to some web location to see if anyone
>> wants to pick up the challenge?
>>
>> I'm not at work so I don't have access to MSSQL, but
>> I seem to remember that you can output/save as XML,
>> which may make things easier (or at least interest a
>> different group of people in having a look).
>>
>> I'm quite certain it can by done by Python; I did
>> consider it myself a couple of months back, but my
>> colleague spotted the problem before I'd really got
>> into the code!
>>
>> TJG
> 
> Good point. Unfortunately, I think our SQL Server must be too old for
> xml (we have version 8). The only save options I see is Trace
> Template, Trace File, Trace Table and SQL Script.

Yes, you're right; I have clients installed for SQL 2000 &
2005 and it's only under 2005 that I have the XML output
option. The .trc file format is pretty much opaque binary,
and the .sql output only gives you the SQL statements
issued - not the events they're associated with.

One obvious way is to save it to a table and to interrogate
that table. I find that kind of thing a bit cumbersome, but
if XML's not an option, it might be the only way. (FWIW,
I find XML cumbersome too, but that might just be lack
of practice ;)

Running a standard trace and saving to a table, this is
the structure which resulted:

CREATE TABLE [trace_output] (
	[RowNumber] [int] IDENTITY (1, 1) NOT NULL ,
	[EventClass] [int] NULL ,
	[TextData] [ntext] COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
	[NTUserName] [nvarchar] (128) COLLATE 
SQL_Latin1_General_CP1_CS_AS NULL ,
	[ClientProcessID] [int] NULL ,
	[ApplicationName] [nvarchar] (128) COLLATE 
SQL_Latin1_General_CP1_CS_AS NULL ,
	[LoginName] [nvarchar] (128) COLLATE 
SQL_Latin1_General_CP1_CS_AS NULL ,
	[SPID] [int] NULL ,
	[Duration] [bigint] NULL ,
	[StartTime] [datetime] NULL ,
	[Reads] [bigint] NULL ,
	[Writes] [bigint] NULL ,
	[CPU] [int] NULL ,
	 PRIMARY KEY  CLUSTERED
	(
		[RowNumber]
	)  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


Seems like you might be able to do something with it.
(Possibly just dumping it straight back out to CSV
or XML if that's easier for you than db querying)

TJG