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 <shonorio@xxxxxxxxxxxx>
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
>
>

.