Mitchell,
Can you generate an ODBC trace to locate exactly how the invalid cursor state
occurs. You can do this from the ODBC Administrator trace tab.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
On 12-Dec-2003 Mitchell, Louise M wrote:
> 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
