If you also look at the tests inside DBD::ODBC (t/20SQLServer.t, specifically), you can see how to get DBCC output...
Jeff > -----Original Message----- > From: David Goodman [mailto:[EMAIL PROTECTED] > Sent: Monday, October 31, 2005 12:39 PM > To: dbi-users > Subject: Re: Can I get message return by DBCC on MS SQL ? > > If you were using DBD::Sybase, you could catch all of these > messages by installing a message handler. > Without that, you would only be able to get the first (I > think the first) through the DBI error string. > > Another approach would be to use isql to execute the sql and > capture the output to a file, and then parse the file for > error strings. The isql utility does not separate row results > from informational and error messages. > > regards, > > David > > --- Solli Moreira Honorio <[EMAIL PROTECTED]> > wrote: > > > I have a Win32::OLE code, like bellow, running to get and > parse a DBCC > > SHOWCONTIG, and a catch the result of DBCC by SQL messages errors. > > > > <code> > > > > use Win32::OLE qw(in); > > use Win32::OLE::Const; > > > > my $DBConn = Win32::OLE->new('ADODB.Connection'); > > $DBConn->Open( "Provider=sqloledb;Data Source=127.0.0.1;Initial > > Catalog=master;User ID=sa; Password=****;" ); > > > > if ( Win32::OLE->LastError() ) { > > print "Error : " . Win32::OLE->LastError() . "\n"; > > exit 1; > > } > > > > my $RS = $DBConn->Execute(q{ > > use [Northwind] > > DECLARE @id int, @indid int, @counter dec (15) CREATE > TABLE #Temp ( > > id int, indid int ) INSERT INTO #Temp > > SELECT o.id, i.indid > > FROM sysobjects AS o > > FULL JOIN sysindexes AS i > > ON o.id = i.id > > WHERE o.xtype = 'U' > > SELECT @counter = COUNT(*) FROM #Temp > > SET rowcount 1 > > WHILE ( @counter ) > 0 > > BEGIN > > SELECT @id = id, @indid = indid FROM #Temp > > SET @counter = @counter -1 > > DBCC SHOWCONTIG (@id, @indid) > > DELETE FROM #Temp WHERE id = @id and indid = @indid END SET > > rowcount 0 DROP TABLE #Temp} ); > > > > if ( Win32::OLE->LastError() ) { > > print "Error : " . Win32::OLE->LastError() . "\n"; > > exit 2; > > } > > > > my @ErrosReturn; > > > > while (1){ > > # Get all message returned by ADO connection > > foreach my $Error ( in ( $DBConn->Errors() ) ) { > > my $Description = $Error->{Description}; > > $Description =~ s/\s*$//; > > push @ErrosReturn, $Description; > > } > > > > eval { $RS = $RS->NextRecordSet(); }; > > last if ( $@ ); > > } > > > > print join "\n", @ErrosReturn; > > </code> > > > > I'm trying to translate, as code bellow, this code for DBI but I > > don't get a way to get the DBCC message. Can i get the DBCC > message on > > DBI ? > > > > <code> > > my $dbcon = DBI->connect ( > > qq{dbi:ODBC:driver={SQL > > Server};Server=127.0.0.1;database=master;}, > > qq{sa}, > > qq{*********} ) > > or die qq{Can't connect to database, erro : > > $DBI::errstr}; > > > > $dbcon->{odbc_exec_direct} = 1; > > > > my $sth = $dbcon->prepare(q{ > > use [Northwind] > > DECLARE @id int, @indid int, @counter dec (15) CREATE > TABLE #Temp ( > > id int, indid int ) INSERT INTO #Temp > > SELECT o.id, i.indid > > FROM sysobjects AS o > > FULL JOIN sysindexes AS i > > ON o.id = i.id > > WHERE o.xtype = 'U' > > SELECT @counter = COUNT(*) FROM #Temp > > SET rowcount 1 > > WHILE ( @counter ) > 0 > > BEGIN > > SELECT @id = id, @indid = indid FROM #Temp > > SET @counter = @counter -1 > > DBCC SHOWCONTIG (@id, @indid) > > DELETE FROM #Temp WHERE id = @id and indid = @indid END SET > > rowcount 0 DROP TABLE #Temp}) || die "Can't prepare > > sql:\n$DBI::errstr\n"; > > > > $sth->execute(); > > > > print $DBI::errstr; > > </code> > > > > Thanks, > > > > Solli M. Honório > > > > > >
