Can Python Parse an MS SQL Trace?

[kyosohma at gmail.com]

On May 8, 5:18 am, Tim Golden <m... at timgolden.me.uk> wrote:
> kyoso... 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

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