By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,923 Members | 1,655 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,923 IT Pros & Developers. It's quick & easy.

Can Python Parse an MS SQL Trace?

P: n/a
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

May 7 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
May 7 '07 #2

P: n/a
On May 7, 8:34 am, Tim Golden <m...@timgolden.me.ukwrote:
kyoso...@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.

Mike

May 7 '07 #3

P: n/a
ky******@gmail.com wrote:
On May 7, 8:34 am, Tim Golden <m...@timgolden.me.ukwrote:
>kyoso...@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
May 8 '07 #4

P: n/a
On May 8, 5:18 am, Tim Golden <m...@timgolden.me.ukwrote:
kyoso...@gmail.com wrote:
On May 7, 8:34 am, Tim Golden <m...@timgolden.me.ukwrote:
kyoso...@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
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

May 8 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.