in reply to How to open SQL 2005 errorlog?

(Side note: your regex probably should be /^\s*([\d\/\-]+\s+[\d\:\.]+) ... I.e., the square brackets are missing — presumably as a result of not using <code> tags for the code section...)

As to the open possibly failing... Did you check whether $ref->{log_open_error} is being set to the error message you're generating ("***Error: ...")? If so, what exactly does it say? What's the value of $sqlErrorlog — is it the correct path/filename?

Replies are listed 'Best First'.
Re^2: How to open SQL 2005 errorlog?
by Anonymous Monk on Jun 06, 2007 at 21:24 UTC
    You are correct! It should be /^\s*([\d\/\-]+\s+[\d\:\.]+) ... It was a result of not using <code> around the script. Sorry for the confusion. Thank you very much for your helping!

    I did check the value of $ref->{log_open_error}. It is blank. And $sqlErrorLog does point to correct path and file name. Please see the sample below. From the output, we see it grabbed the first link of log for SQL 2000 error. For SQL 2005, however, it showed 'yp2'.

    Sample script:

    ...
# now open the errorlog file for scan
unless (open(LOG, "$sqlErrorlog")) {
        $ref->{log_open_error} = "***Error: could not open $sqlErrorlo
+g for read. ";
        $ref->{log_open_error} .= Win32::FormatMessage(Win32::GetLastE
+rror);
        return $ref;
}
print "Error: $ref->{log_open_error}\n";
print "$sqlErrorlog\n";
# get the very first errorlog line. It has the version info.
$_ = <LOG>;
print "$_\n";
if (/^\s*([\d\/\-]+\s+[\d\:\.]+)\s+(?:kernel|Server|spid\d+)\s+(Micros
+oft\s+SQL\s+Server.+)/i) {
          ($ref->{first_recorded_date}, $ref->{sql_version}) = ($1, $2
+);
print " DT: $ref->{first_recorded_date}\n";
print " Version: $2\n";
    }

    [download]
    The output is:
    Error:
    \\SQL2000Server\d$\MSSQL\LOG\ErrorLog
    2007-05-30 08:29:03.17 server Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
    DT: 2007-05-30 08:29:03.17
    Version: Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
    ...
    Error:
    \\SQL2005Server\d$\MSSQL\LOG\ERRORLOG
    ÿþ2

[reply]
[d/l]
[select]

      Ahh...  the first two bytes (ÿþ = 0xFF 0xFE) most likely is a BOM (Byte Order Mark), indicating that the file is UTF-16le or UCS-2le (le=little-endian) encoded (the distinction between UTF-16 and UCS-2 is probably irrelevant in your case — what's essential is that (at least) two bytes are being used to encode a single character).

      In order to properly read such files, you need to open them like this: 

      unless (open LOG, "<:encoding(ucs-2)", $sqlErrorlog) { ...

      [download]

      or 

      unless (open LOG, "<:encoding(utf-16)", $sqlErrorlog) { ...

      [download]

      (you need Perl 5.8.x for this to work)

      When debug-printing your $_, you should then see the line content as expected.

      Just in case you're still having problems (in particular with line endings), you might want to see this post of mine, which describes the problem, and a workaround. Good luck.

[reply]
[d/l]
[select]
        WOW, You are GREAT! utf-16 works for SQL 2005 error log! Thank you soooooooo much!!

        Can you give me one more help? How should I change this script so it will be able to open error logs of both SQL 2000 and SQL 2005? utf-16 does not work with SQL 2000 log.

        I am new in Perl. I really appreciate for your help!

[reply]

In Section Seekers of Perl Wisdom