[dba-SQLServer] Opening a log and keeping it open (cross-posted to dba-sql)

Arthur Fuller artful at rogers.com
Sun Jul 10 12:11:03 CDT 2005


I've got a procedure called ExecuteCommand (which perhaps ought to have been
named ExecuteSQLCommand, but anyway). It encapsulates a bunch of stuff like
the current project connection and such, and contains debug code that prints
the SQL it is attempting to execute, and also an error handler that reports
"Problem executing " + the SQL code it received.

This is all very nice and works splendidly. However, the debug window has
decided limitations, so I've been thinking that I should write all this
information to a sort of transaction log. Basically, take what I now print
to the debug window and re-direct it to an ASCII file of a known name, etc. 

I have worked with reading and writing ASCII text files so I'm not a
complete naïf in this regard, but I do have a question. Suppose that I
revise the code such that the first time ExcuteCommand is called it opens
the file, creating it if necessary, and leaves it open. Every subsequent
visit to Execute Command finds the file open and simply appends its SQL
statement, just as it would to the debug window... but with the advantage
that I could have several hours' worth of statements recorded to the file,
rather than merely the last N lines of the debug buffer.

1. Would the app suffer in terms of performance if I did this?
2. Assuming that I opened the file in the usual way (as #1 or whatever), and
assuming that somewhere in the app I also open another text file, should I
protect myself by opening it as #98765 or somesuch, so that if you drop said
module into one of your apps, it would be guaranteed to work even if your
app opens a dozen other text files at various points?
3. Presumably the code would also need a "close the file" chunk, but in the
ideal world I wouldn't want you to have to code any special thing into your
"close app" procedure. So perhaps this might be better dealt with as a
class? But I seem to recall from Shamil's writing on classes that there is
an issue regarding the proper termination and garbage disposal of a class.

Any suggestions, anyone?
Arthur




More information about the dba-SQLServer mailing list