Can I get message return by DBCC on MS SQL ?
- From: shonorio@xxxxxxxxxxxx (Solli Moreira Honorio)
- Date: Mon, 31 Oct 2005 14:15:59 -0300
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
.
- Follow-Ups:
- Re: Can I get message return by DBCC on MS SQL ?
- From: David Goodman
- Re: Can I get message return by DBCC on MS SQL ?
- Prev by Date: DBD mysql loosing connection and failing to reconnect
- Next by Date: Re: Can I get message return by DBCC on MS SQL ?
- Previous by thread: DBD mysql loosing connection and failing to reconnect
- Next by thread: Re: Can I get message return by DBCC on MS SQL ?
- Index(es):