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
> >
> >
> >
> >
>
--
David Good [EMAIL PROTECTED]
This space intentionally left blank.
