Can Python Parse an MS SQL Trace?

[kyosohma]

Hi All,

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.

Any pointers are appreciated.

Thanks!

Mike

[Tim Golden]

ky******@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

[kyosohma]

On May 7, 8:34 am, Tim Golden <[e-mail]:

[e-mail] 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.

Mike

[Tim Golden]

ky******@gmail.com wrote:

On May 7, 8:34 am, Tim Golden <[e-mail]:

>[e-mail] 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

[kyosohma]

On May 8, 5:18 am, Tim Golden <[e-mail]:

[e-mail] wrote:

On May 7, 8:34 am, Tim Golden <[e-mail]:

[e-mail] 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

Thanks for the advice. I did the one that had to be done now by hand.
However, I know I'll need to do more of these in the future, so I'll
try it then.

Mike