Martin, et.al.,
Getting back to this and still having difficulties... always get the
'Invalid cursor state' error message with many of the DBCC commands
executed thru the DBD::ODBC.
Here is a bit of code anyone could execute ( change server name )...
it's a simple small test... below is the code and below that the
output... nothing I do seems to work here. I am executing on a Windows
server, using the most up-to-date DBI, DBD::ODBC, and MDAC...
Any help is appreciated.
Thanks,
Louise Mitchell
*************
CODE
*************
#!d:\apps\perl\bin\perl.exe
use DBI;
# using latest version of DBI and MDAC and DBD::ODBC....
#
$dbh = 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 = qq%begin
dbcc traceon(3604)
DBCC INDEXDEFRAG(pps,cost_sheet,ix_cost_sheet_u1)
end%;
print "** command: \n$command\n";
$sth = $dbh->prepare($command);
die $DBI::errstr unless $sth;
$rc = $sth->execute(); # This is the 'do it'.....
$rows = $sth->dump_results();
print "** return code: $rc \n";
print "** rows: $rows\n";
print "\nCOMMAND OUTPUT (from odbc_err_handler):\n$err_text\n";
$dbh->disconnect;
*************
OUTPUT
*************
** command:
begin
dbcc traceon(3604)
DBCC INDEXDEFRAG(pps,cost_sheet,ix_cost_sheet_u1)
end
0 rows (-1: (DBD: no select statement currently executing err=-1))
** return code:
** rows: 0
COMMAND OUTPUT (from odbc_err_handler):
DBCC execution completed. If DBCC printed error messages, contact your
system administrator. Invalid cursor state
Tool completed successfully
-----Original Message-----
From: Martin J. Evans [mailto:[EMAIL PROTECTED]
Sent: Saturday, December 13, 2003 2:01 AM
To: Mitchell, Louise M
Subject: RE: succint view of problem - getting results from DBCC
INDEXDEFRAG - DBD::ODBC
Mitchell,
OK, I get different results here.
I am not running the perl on Windows but on Linux through our ODBC-ODBC
Bridge to the MS SQL Server ODBC driver on Windows. My SQLNumResults
call does not fail but it does return 0 columns which makes DBD::ODBC
think there is no result-set - hence the "no select statement currently
executing".
The difference between our results is probably a difference in the MS
SQL Server ODBC driver - I'd suggest getting the latest MDAC - see other
postings on the list from Jeff Urlwin. However, this is not going to
make any difference to the final result since DBREINDEX does not appear
to be returning a result-set.
>From my OOB log (my comments start #):
SQLExecute(0x8268f78)
^put_bound_parameters(0x8268f78,0x824a960,1)
Driver supports SQLNumParams : 1
remote sql_num_params()=0 (return parameters=0)
-^put_bound_parameters()=SQL_SUCCESS (nparams <= 0) -SQLExecute(...)=1 #
SQLExecute returns SQL_SUCCESS_WITH_INFO which means there is an ODBC #
diagnostic to pick up
SQLGetDiagRec(3,0x8268f78,1,0xbffff278,0xbffff274,0xbffff280,512,0xbffff
270)
0 records on client according to header
record 1 NOT found on client
Now looking for error record 1 on server -SQLGetDiagRec()=0
SQLGetDiagRec(3,0x8268f78,2,0xbffff278,0xbffff274,0xbffff280,512,0xbffff
270)
0 records on client according to header
record 2 NOT found on client
Now looking for error record 2 on server -SQLGetDiagRec()=100 #
these diags appear to be: # sqlstate=01000 native=2528 "[NetConn:
0627bd50][Microsoft][ODBC SQL Server # Driver][SQL Server]DBCC execution
completed. If DBCC printed error messages, # contact your system
administrator."
SQLRowCount(0x8268f78,0x824b01c)
-SQLRowCount(RowCount=-1)=0
SQLNumResultCols(0x8268f78,0xbffff6de)
-SQLNumResultCols()=0 (value=0)
# SQLNumResultCols returns 0 columns in result-set - hence no result-set
SQLMoreResults(0x8268f78)
^oob_new_result_set(0x8268f78,0,1)
^retrieve_server_diags(3,0x8268f78,3,0xaf1780)
1 diags found in server
^post_error(0x82690c8,8,1,0,(nil),0x824adc0,2528,0,0x40274227,0xbffff544
,0xbffff144)
-^post_error()
-^retrieve_server_diags()
-^oobc_new_result_set()=0 (f=0x21)
-SQLMoreResults()=1
# SQLMoreResults returns SQL_SUCCESS_WITH_INFO
# so more diags to pick up
SQLGetDiagRec(3,0x8268f78,1,0xbffff128,0xbffff124,0xbffff130,512,0xbffff
120)
1 records on client according to header
Found error record 1 on client
-SQLGetDiagRec()=0
SQLGetDiagRec(3,0x8268f78,2,0xbffff128,0xbffff124,0xbffff130,512,0xbffff
120)
1 records on client according to header
record 2 NOT found on client
Now looking for error record 1 on server -SQLGetDiagRec()=100 #
these diags appear to be the same as above - again.
SQLNumResultCols(0x8268f78,0xbffff6de)
-SQLNumResultCols()=0 (value=0)
# Again, no resulting columsn hence no result-set
SQLMoreResults(0x8268f78)
-SQLMoreResults()=100
# no more result-sets
So no result-sets were created by the reindex. The example on MS site
suggests you should see something like:
Index (ID = 1) is being rebuilt.
Perhaps you don't get these if the index does not need rebuilding.
I don't mind being quoted on dbi-users.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
On 13-Dec-2003 Martin J. Evans wrote:
> Mitchell,
>
> Forget that, I've reproduced here.
>
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> Development
>
>
> On 13-Dec-2003 Martin J. Evans wrote:
>> Mitchell,
>>
>> I don't see why SQLNumResultCols returns an invalid cursor state but
>> it may be something to do with the SQLExecDirect returning a
>> SQL_SUCCESS_WITH_INFO. Is it
>> at all possible you could mail me your code and the info to allow me
to run
>> this against my SQL Server and I'll take a look.
>>
>> I see from the MS site that:
>>
>> DBCC DBREINDEX returns this result set (message) if the NO_INFOMSGS
>> option is
>> specified:
>>
>> DBCC execution completed. If DBCC printed error messages, contact
>> your system administrator.
>>
>> which is what you got without specifying WITH NO_INFOMSGS.
>>
>> Martin
>> --
>> Martin J. Evans
>> Easysoft Ltd, UK
>> Development
>>
>> On 12-Dec-2003 Mitchell, Louise M wrote:
>>> Martin,
>>>
>>> I did as you suggested, but am not sure how to interpret the
>>> results... I've included it below... could you possibly take a
>>> look... it seems to error about a SQLNumResultCols call....which is
>>> confusing to me... this command (DBCC REINDEX) doesn't return a
>>> result set...
>>>
>>> L
>>> process_engine 9a0-81c ENTER SQLAllocHandle
>>> SQLSMALLINT 3 <SQL_HANDLE_STMT>
>>> SQLHANDLE 01F32400
>>> SQLHANDLE * 01BA0CA8
>>>
>>> process_engine 9a0-81c EXIT SQLAllocHandle with return code
0
>>> (SQL_SUCCESS)
>>> SQLSMALLINT 3 <SQL_HANDLE_STMT>
>>> SQLHANDLE 01F32400
>>> SQLHANDLE * 0x01BA0CA8 ( 0x01f32d10)
>>>
>>> process_engine 9a0-81c ENTER SQLPrepare
>>> HSTMT 01F32D10
>>> UCHAR * 0x01BA59A4 [ 82] "begin\
adbcc
>>> traceon(3604)\ aDBCC
>>> DBREINDEX('pps.dbo.cost_sheet',ix_cost_sheet_ui)\
>>> aend"
>>> SDWORD 82
>>>
>>> process_engine 9a0-81c EXIT SQLPrepare with return code 0
>>> (SQL_SUCCESS)
>>> HSTMT 01F32D10
>>> UCHAR * 0x01BA59A4 [ 82] "begin\
adbcc
>>> traceon(3604)\ aDBCC
>>> DBREINDEX('pps.dbo.cost_sheet',ix_cost_sheet_ui)\
>>> aend"
>>> SDWORD 82
>>>
>>> process_engine 9a0-81c ENTER SQLFreeStmt
>>> HSTMT 01F32D10
>>> UWORD 3 <SQL_RESET_PARAMS>
>>>
>>> process_engine 9a0-81c EXIT SQLFreeStmt with return code 0
>>> (SQL_SUCCESS)
>>> HSTMT 01F32D10
>>> UWORD 3 <SQL_RESET_PARAMS>
>>>
>>> process_engine 9a0-81c ENTER SQLExecute
>>> HSTMT 01F32D10
>>>
>>> process_engine 9a0-81c EXIT SQLExecute with return code 1
>>> (SQL_SUCCESS_WITH_INFO)
>>> HSTMT 01F32D10
>>>
>>> DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL
>>> Server]DBCC execution completed. If DBCC printed error messages,
>>> contact your system administrator. (2528)
>>>
>>> process_engine 9a0-81c ENTER SQLErrorW
>>> HENV 01F31788
>>> HDBC 01F32400
>>> HSTMT 01F32D10
>>> WCHAR * 0x0140F924 (NYI)
>>> SDWORD * 0x0140FB68
>>> WCHAR * 0x0140F524
>>> SWORD 511
>>> SWORD * 0x0140FB8A
>>>
>>> process_engine 9a0-81c EXIT SQLErrorW with return code 0
>>> (SQL_SUCCESS)
>>> HENV 01F31788
>>> HDBC 01F32400
>>> HSTMT 01F32D10
>>> WCHAR * 0x0140F924 (NYI)
>>> SDWORD * 0x0140FB68 (2528)
>>> WCHAR * 0x0140F524 [ 139]
>>> "[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution
>>> completed. If DBCC printed error messages, contact your system
>>> administrator."
>>> SWORD 511
>>> SWORD * 0x0140FB8A (139)
>>>
>>> process_engine 9a0-81c ENTER SQLErrorW
>>> HENV 01F31788
>>> HDBC 01F32400
>>> HSTMT 01F32D10
>>> WCHAR * 0x0140F924 (NYI)
>>> SDWORD * 0x0140FB68
>>> WCHAR * 0x0140F524
>>> SWORD 511
>>> SWORD * 0x0140FB8A
>>>
>>> process_engine 9a0-81c EXIT SQLErrorW with return code 100
>>> (SQL_NO_DATA_FOUND)
>>> HENV 01F31788
>>> HDBC 01F32400
>>> HSTMT 01F32D10
>>> WCHAR * 0x0140F924 (NYI)
>>> SDWORD * 0x0140FB68
>>> WCHAR * 0x0140F524
>>> SWORD 511
>>> SWORD * 0x0140FB8A
>>>
>>> process_engine 9a0-81c ENTER SQLErrorW
>>> HENV 01F31788
>>> HDBC 01F32400
>>> HSTMT 00000000
>>> WCHAR * 0x0140F924 (NYI)
>>> SDWORD * 0x0140FB68
>>> WCHAR * 0x0140F524
>>> SWORD 511
>>> SWORD * 0x0140FB8A
>>>
>>> process_engine 9a0-81c EXIT SQLErrorW with return code 100
>>> (SQL_NO_DATA_FOUND)
>>> HENV 01F31788
>>> HDBC 01F32400
>>> HSTMT 00000000
>>> WCHAR * 0x0140F924 (NYI)
>>> SDWORD * 0x0140FB68
>>> WCHAR * 0x0140F524
>>> SWORD 511
>>> SWORD * 0x0140FB8A
>>>
>>> process_engine 9a0-81c ENTER SQLErrorW
>>> HENV 01F31788
>>> HDBC 00000000
>>> HSTMT 00000000
>>> WCHAR * 0x0140F924 (NYI)
>>> SDWORD * 0x0140FB68
>>> WCHAR * 0x0140F524
>>> SWORD 511
>>> SWORD * 0x0140FB8A
>>>
>>> process_engine 9a0-81c EXIT SQLErrorW with return code 100
>>> (SQL_NO_DATA_FOUND)
>>> HENV 01F31788
>>> HDBC 00000000
>>> HSTMT 00000000
>>> WCHAR * 0x0140F924 (NYI)
>>> SDWORD * 0x0140FB68
>>> WCHAR * 0x0140F524
>>> SWORD 511
>>> SWORD * 0x0140FB8A
>>>
>>> process_engine 9a0-81c ENTER SQLRowCount
>>> HSTMT 01F32D10
>>> SQLLEN * 0x01BA0CD0
>>>
>>> process_engine 9a0-81c EXIT SQLRowCount with return code 0
>>> (SQL_SUCCESS)
>>> HSTMT 01F32D10
>>> SQLLEN * 0x01BA0CD0 (-1)
>>>
>>> process_engine 9a0-81c ENTER SQLNumResultCols
>>> HSTMT 01F32D10
>>> SWORD * 0x0140FBBA
>>>
>>> process_engine 9a0-81c EXIT SQLNumResultCols with return
code -1
>>> (SQL_ERROR)
>>> HSTMT 01F32D10
>>> SWORD * 0x0140FBBA
>>>
>>> DIAG [24000] [Microsoft][ODBC SQL Server
>>> Driver]Invalid cursor state (0)
>>>
>>> process_engine 9a0-81c ENTER SQLErrorW
>>> HENV 01F31788
>>> HDBC 01F32400
>>> HSTMT 01F32D10
>>> WCHAR * 0x0140F7F0 (NYI)
>>> SDWORD * 0x0140FA34
>>> WCHAR * 0x0140F3F0
>>> SWORD 511
>>> SWORD * 0x0140FA56
>>>
>>> process_engine 9a0-81c EXIT SQLErrorW with return code 0
>>> (SQL_SUCCESS)
>>> HENV 01F31788
>>> HDBC 01F32400
>>> HSTMT 01F32D10
>>> WCHAR * 0x0140F7F0 (NYI)
>>> SDWORD * 0x0140FA34 (0)
>>> WCHAR * 0x0140F3F0 [ 55]
>>> "[Microsoft][ODBC SQL Server Driver]Invalid cursor state"
>>> SWORD 511
>>> SWORD * 0x0140FA56 (55)
>>>
>>> process_engine 9a0-81c ENTER SQLErrorW
>>> HENV 01F31788
>>> HDBC 01F32400
>>> HSTMT 01F32D10
>>> WCHAR * 0x0140F7F0 (NYI)
>>> SDWORD * 0x0140FA34
>>> WCHAR * 0x0140F3F0
>>> SWORD 511
>>> SWORD * 0x0140FA56
>>>
>>> process_engine 9a0-81c EXIT SQLErrorW with return code 100
>>> (SQL_NO_DATA_FOUND)
>>> HENV 01F31788
>>> HDBC 01F32400
>>> HSTMT 01F32D10
>>> WCHAR * 0x0140F7F0 (NYI)
>>> SDWORD * 0x0140FA34
>>> WCHAR * 0x0140F3F0
>>> SWORD 511
>>> SWORD * 0x0140FA56
>>>
>>> process_engine 9a0-81c ENTER SQLErrorW
>>> HENV 01F31788
>>> HDBC 01F32400
>>> HSTMT 00000000
>>> WCHAR * 0x0140F7F0 (NYI)
>>> SDWORD * 0x0140FA34
>>> WCHAR * 0x0140F3F0
>>> SWORD 511
>>> SWORD * 0x0140FA56
>>>
>>> process_engine 9a0-81c EXIT SQLErrorW with return code 100
>>> (SQL_NO_DATA_FOUND)
>>> HENV 01F31788
>>> HDBC 01F32400
>>> HSTMT 00000000
>>> WCHAR * 0x0140F7F0 (NYI)
>>> SDWORD * 0x0140FA34
>>> WCHAR * 0x0140F3F0
>>> SWORD 511
>>> SWORD * 0x0140FA56
>>>
>>> process_engine 9a0-81c ENTER SQLErrorW
>>> HENV 01F31788
>>> HDBC 00000000
>>> HSTMT 00000000
>>> WCHAR * 0x0140F7F0 (NYI)
>>> SDWORD * 0x0140FA34
>>> WCHAR * 0x0140F3F0
>>> SWORD 511
>>> SWORD * 0x0140FA56
>>>
>>> process_engine 9a0-81c EXIT SQLErrorW with return code 100
>>> (SQL_NO_DATA_FOUND)
>>> HENV 01F31788
>>> HDBC 00000000
>>> HSTMT 00000000
>>> WCHAR * 0x0140F7F0 (NYI)
>>> SDWORD * 0x0140FA34
>>> WCHAR * 0x0140F3F0
>>> SWORD 511
>>> SWORD * 0x0140FA56
>>>
>>> process_engine 9a0-81c ENTER SQLFreeHandle
>>> SQLSMALLINT 3 <SQL_HANDLE_STMT>
>>> SQLHANDLE 01F32D10
>>>
>>> process_engine 9a0-81c EXIT SQLFreeHandle with return code
0
>>> (SQL_SUCCESS)
>>> SQLSMALLINT 3 <SQL_HANDLE_STMT>
>>> SQLHANDLE 01F32D10
>>>
>>> process_engine 9a0-81c ENTER SQLAllocHandle
>>> SQLSMALLINT 3 <SQL_HANDLE_STMT>
>>> SQLHANDLE 01F31830
>>> SQLHANDLE * 0140FAAC
>>>
>>> process_engine 9a0-81c EXIT SQLAllocHandle with return code
0
>>> (SQL_SUCCESS)
>>> SQLSMALLINT 3 <SQL_HANDLE_STMT>
>>> SQLHANDLE 01F31830
>>> SQLHANDLE * 0x0140FAAC ( 0x01f32d10)
>>>
>>> -----Original Message-----
>>> From: Martin J. Evans [mailto:[EMAIL PROTECTED]
>>> Sent: Friday, December 12, 2003 12:58 AM
>>> To: [EMAIL PROTECTED]
>>> Subject: RE: succint view of problem - getting results from DBCC
>>> INDEXDEFRAG - DBD::ODBC
>>>
>>>
>>> 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
