Re: get the messages, not the rows

[Chuck Fox]

David,

In regards to the buffering, in Sybase and MS there is a set command SET 
FLUSHMESSAGE ON, that will force the buffers to flush from OpenClient to 
the OS.

HTH,

Chuck Fox

[EMAIL PROTECTED] wrote:

On Sat, Sep 13, 2003 at 01:08:27PM -0400, Jeff Urlwin <[EMAIL PROTECTED]> wrote:
 

Is there a way to execute an SQL command and get the 
messages, not the records?

I mean, I'd like to run
            DBCC CHECKDB ('databasename')
parse the output and add the result to a daily report of my 
servers' health. The question is how do I get the messages.

I'm using DBI+DBD::ODBC and MS SQL Server 2000.
     

Buried in the DBD::ODBC t/20SqlServer.t is an example.  You need to set an
error handler and set odbc_async_exec.  In there is the snippet that calls
"dbcc TRACESTATUS(-1)" which runs during the tests (the safest thing I think
I could find to run on generic tests, but I believe DBCC
CHECKDB('databasename') was the reason the original patches were submitted
by David L. Good and included by me...  The only "clear" documentation is in
the Changes file.  The Changes file also points to a specific file which
calls checkdb...
   



You don't really need to set odbc_async_exec unless you need to get the
messages as soon as possible after they are generated.  In my case, I wanted
to be able to send an alert as soon as my program saw a DBCC error but still
let the DBCC continue.  Unfortunately, it seems that ODBC buffers the output,
so even with odbc_async_exec set you get the messages in chunks, so an error
could sit in a buffer for quite some time (say, while a really large table
is being DBCC'd) before you'll see it.
 

use strict;

use DBI;

sub err_handler {
  my ($state, $msg) = @_;
  # Strip out all of the driver ID stuff
  $msg =~ s/^(\[[\w\s]*\])+//;
  print "===> state: $state msg: $msg\n";
  return 0;
}
my $dbh = DBI->connect("dbi:ODBC:PERL_TEST_SQLSERVER", $ENV{DBI_USER},
$ENV{DBI_PASS})
      || die "Can't connect: $DBI::errstr\n";
$dbh->{odbc_err_handler} = \&err_handler;
$dbh->{odbc_async_exec} = 1;
print "odbc_async_exec is: $dbh->{odbc_async_exec}\n";
my $sth;
$sth = $dbh->prepare("dbcc checkdb(model)") || die $dbh->errstr;
$sth->execute                               || die $dbh->errstr;
$sth->finish;
$dbh->disconnect;
Regards,

Jeff

   

Thanks, Jenda
(And sorry if it's on line XX of the fine manual and I just 
did not find it:) ===== [EMAIL PROTECTED] === 
http://Jenda.Krynicky.cz ===== When it comes > to wine, women 
and song, wizards are allowed to get drunk and croon as much 
as they like.
            -- Terry Pratchett in Sourcery