On May 8, 5:18 am, Tim Golden <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > On May 7, 8:34 am, Tim Golden <[EMAIL PROTECTED]> wrote: > >> [EMAIL PROTECTED] 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 -- http://mail.python.org/mailman/listinfo/python-list
