All,
I've posted bits of this earlier, but here is the problem in a
nutshell... the code below executes DBCC INDEXDEFRAG in a MS SQL
Server... I did some research on the 'invalid cursor state' that you'll
see below in the output... this command returns a result set, so I
realized I needed to retrieve that separately...that did not solve the
problem... so the bottom line is... how do I get the result set from
this sort of command... and also get the message output ( that's working
right now...)
Other commands, such as DBCC CHECKDB...don't return result sets, so I
get all of the output handled in the odbc_err_handler...
Here's the basic code..... results are below...you can see that the
'dump_results' method didn't have anything to work on...
Does anyone have any clues here?
Thanks,
LouiseM
*************************
code
***********************
#!d:\apps\perl\bin\perl.exe
use DBI;
$dbh_actions = DBI->connect
("dbi:ODBC:Driver={SQL
Server};Server=irmdm2;Trusted_Connection=yes;",'','',
{ PrintError => 0,
RaiseError => 0,
LongReadLen => 65536,
odbc_async_exec => 0,
odbc_err_handler => sub {
my ($state, $msg) = @_;
# Strip out all of the driver ID
stuff
$msg =~ s/^(\[[\w\s]*\])+//;
$err_text .= $msg."\n";
return 0;
}
}
);
$command = 'begin dbcc traceon(3604) DBCC
INDEXDEFRAG(pps,cost_sheet,ix_cost_sheet_u1) end';
$sth = $dbh_actions->prepare($command);
die $DBI::errstr unless $sth;
$rc = $sth->execute(); # This is the 'do it'.....
$rows = $sth->dump_results();
print "** rows: $rows\n";
print "\nCOMMAND OUTPUT:\n$err_text\n";
$dbh_actions->disconnect;
*************************
RESULTS
*************************
0 rows (-1: (DBD: no select statement currently executing err=-1))
** rows: 0
COMMAND OUTPUT:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Invalid cursor state
