FWD: RE: [dbi] RE: succint view of problem - getting results from DBCC INDEXDEFRAG - DBD::ODBC
From: Martin J. Evans (martin_at_easysoft.com)
Date: 12/30/03
- Next message: Martin J. Evans: "coredump in perl using DBD::ODBC (was succint view of problem - getting results from DBCC)"
- Previous message: Jeff Urlwin: "RE: Oracle.dll load Failure V5.8.1"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Tue, 30 Dec 2003 18:02:54 -0000 (GMT) To: dbi-users@perl.org
I answered this email sent to me then it was resent to dbi-users.
Here is what I said:
-----FW: <XFMail.20031230180043.martin@easysoft.com>-----
Date: Tue, 30 Dec 2003 18:00:43 -0000 (GMT)
From: "Martin J. Evans" <martin@easysoft.com>
To: "Mitchell, Louise M" <Louise.Mitchell@pnl.gov>
Subject: RE: [dbi] RE: succint view of problem - getting results from DBCC
INDEXDEFRAG - DBD::ODBC
Louise,
> Martin, et.al.,
I seemed to be the only one in the list.
I still think you have an older SQL Server driver as I do not get the call to
SQLNumResultCols failing. I am using SQL Server ODBC Driver v 2000.81.9030.04.
If I modify your script to:
#!/usr/bin/perl -w
use DBI;
# using latest version of DBI and MDAC and DBD::ODBC....
#
$dbh = DBI->connect("dbi:ODBC:test", 'Martin_Evans','easysoft',
{ PrintError => 0,
RaiseError => 1,
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%
dbcc traceon(3604)
dbcc indexdefrag(test,mje2,mje2i)%;
print "** command: \n$command\n";
$sth = $dbh->prepare($command);
die $DBI::errstr unless $sth;
$rc = $sth->execute(); # This is the 'do it'.....
do {
my @row;
print "RESULT:\n";
while (@row = $sth->fetchrow_array()) {
print "Data: ", join(",", @row), "\n";
}
} while ($sth->{odbc_more_results});
#$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;
and run it on a table:
create table mje2(a int)
create index mje2i on mje2 (a)
I get:
Name "main::rows" used only once: possible typo at ./x.pl line 42.
** command:
dbcc traceon(3604)
dbcc indexdefrag(test,mje2,mje2i)
RESULT:
Data: 0,0,0
RESULT:
DBD::ODBC::st fetchrow_array failed: (DBD: st_fetch/SQLFetch err=-1) at ./x.pl
line 35.
Segmentation fault
Note the result of 0,0,0 which you don't get. It should not core dump and I'm
investigating that - something to do with the dump_results (BTW, adding
DBI->trace(6); to the start of the script stops the core dump :-( ). With your
script (using dump_results) I get:
** command:
dbcc traceon(3604)
dbcc indexdefrag(test,mje2,mje2i)
'0', '0', '0'
1 rows
** return code: -1
** rows: 1
COMMAND OUTPUT (from odbc_err_handler):
[NetConn: 0198c638][Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
execution completed. If DBCC printed error messages, contact your system
administrator.
DBI::db=HASH(0x81af90c)->disconnect invalidates 1 active statement handle
(either destroy statement handles or call finish on them before disconnecting)
at ./x.pl line 38.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
On 30-Dec-2003 Mitchell, Louise M wrote:
> 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:martin@easysoft.com]
> 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:martin@easysoft.com]
>>>> Sent: Friday, December 12, 2003 12:58 AM
>>>> To: dbi-users@perl.org
>>>> 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
--------------End of forwarded message-------------------------
--
Martin J. Evans
Easysoft Ltd, UK
Development
- Next message: Martin J. Evans: "coredump in perl using DBD::ODBC (was succint view of problem - getting results from DBCC)"
- Previous message: Jeff Urlwin: "RE: Oracle.dll load Failure V5.8.1"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
