Can I get message return by DBCC on MS SQL ?

I have a Win32::OLE code, like bellow, running to get and parse a DBCC SH=
OWCONTIG, and a catch the result of
DBCC by SQL messages errors.



use Win32::OLE qw(in);
use Win32::OLE::Const;

my $DBConn =3D Win32::OLE->new('ADODB.Connection');
$DBConn->Open( "Provider=3Dsqloledb;Data Source=3D127.0.0.1;Initial Ca=
talog=3Dmaster;User ID=3Dsa; Password=3D****;" );

if ( Win32::OLE->LastError() ) {
print "Error : " . Win32::OLE->LastError() . "\n";
exit 1;
}

my $RS =3D $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 =3D i.id
WHERE o.xtype =3D 'U'
SELECT @counter =3D COUNT(*) FROM #Temp
SET rowcount 1
WHILE ( @counter ) > 0
BEGIN
SELECT @id =3D id, @indid =3D indid FROM #Temp
SET @counter =3D @counter -1
DBCC SHOWCONTIG (@id, @indid)
DELETE FROM #Temp WHERE id =3D @id and indid =3D @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 =3D $Error->{Description};
$Description =3D~ s/\s*$//;
push @ErrosReturn, $Description;
}

eval { $RS =3D $RS->NextRecordSet(); };
last if ( $@ );
}

print join "\n", @ErrosReturn;


I'm trying to translate, as code bellow, this code for DBI but I don't g=
et a way to get the DBCC message. Can
i get the DBCC message on DBI ?


my $dbcon =3D DBI->connect (
qq{dbi:ODBC:driver=3D{SQL Server};Server=3D127.0.0.1;database=3Dmaster=
;},
qq{sa},
qq{*********} )
or die qq{Can't connect to database, erro : $DBI::errstr};

$dbcon->{odbc_exec_direct} =3D 1;

my $sth =3D $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 =3D i.id
WHERE o.xtype =3D 'U'
SELECT @counter =3D COUNT(*) FROM #Temp
SET rowcount 1
WHILE ( @counter ) > 0
BEGIN
SELECT @id =3D id, @indid =3D indid FROM #Temp
SET @counter =3D @counter -1
DBCC SHOWCONTIG (@id, @indid)
DELETE FROM #Temp WHERE id =3D @id and indid =3D @indid
END
SET rowcount 0
DROP TABLE #Temp}) || die "Can't prepare sql:\n$DBI::errstr\n";

$sth->execute();

print $DBI::errstr;


Thanks,

Solli M. Hon=F3rio

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
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.
>
>
>
> 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;
>
>
> 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 ?
>
>
> 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;
>
>
> Thanks,
>
> Solli M. Honório
>
>

RE: Can I get message return by DBCC on MS SQL ?

If you also look at the tests inside DBD::ODBC (t/20SQLServer.t, =
specifically), you can see how to get DBCC output...

Jeff=20

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