in reply to Re: How to open SQL 2005 errorlog?
in thread How to open SQL 2005 errorlog?

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

Replies are listed 'Best First'.
Re^3: How to open SQL 2005 errorlog?
by almut (Canon) on Jun 06, 2007 at 22:50 UTC

    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]

        I would just switch the open mode, depending on whether it's a 2005 or pre-2005 logfile. In its most simple case, looking for the "2005" in the pathname 

        my $openmode = $sqlErrorlog =~ /2005/ ? ">:encoding(utf-16)" : ">";

unless (open LOG, $openmode, $sqlErrorlog) { ...

        [download]

        (or, as a refinement, you could also check if the year is >= 2005 — presuming they switched to unicode with SQL Server 2005, and future versions will adhere to the same naming scheme, i.e. 2007, 2009, ...   though that might be too many presumptions :)

        However, that approach is kinda cheating, and not generally recommended programming practice (reason: when you/someone happens to change the pathname some time in the future, it's very unlikely you're going to remember this curious dependency buried deep down in the code...).

        So, another way would be to test for the existence of the BOM, and if there, set UTF-16 mode. Actually - as I only recently learned myself - there's already a module for this: File::BOM, which handles the issue even more generally... (see the docs for examples).

[reply]
[d/l]

In Section Seekers of Perl Wisdom