Chuck,
That helped somewhat....it caused the final 'DBCC execution
completed....' output to get sent... but the rest of the output is still
lost...and now there is an 'invalid cursor state' message
Here is what the output looks like in query analyzer:
-------------------------------
OUTPUT from Query Analyzer:
-------------------------------
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
0 0 0
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
Here is the 'log' I'm generating from this process...note that the DBCC
CHECKDB results all get caught in the STDERR handler, without the
traceon... and also note that the final statement for a DBCC command is
always the same... so from the behavior with the DBCC CHECKDB, I would
assumed that ALL of the output, including that last statement are
getting sent to STDERR
When I don't have traceon, I get NO results at all for the DBCC
INDEXDEFRAG (see command below). When it is on ( as shown below ), I
get that final statement... AND... and 'Invalid cursor state'
message...maybe this is why I don' get the actual statistics, but since
I'm executing the same thing, I'm uncertain about it...
Any ideas? I will continue looking for clues...
Thanks,
L
********************************************************************
** Processing....group_name2 - 38 - DBCC_CHECKDB - 900005.0
** Command: dbcc checkdb ('shims')
********************************************************************
COMMAND OUTPUT:
DBCC results for 'shims'.
DBCC results for 'sysobjects'.
There are 21 rows in 1 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 31 rows in 1 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 256 rows in 4 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 92 rows in 4 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 18 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 15 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 196 rows in 1 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 0 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
CHECKDB found 0 allocation errors and 0 consistency errors in
database 'SHIMS'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
********************************************************************
** Processing....group_name2 - 37 - DBCC_INDEXDEFRAG - 1000004.0
** Command: begin
dbcc traceon(3604)
DBCC INDEXDEFRAG(pps,cost_sheet,ix_cost_sheet_u1)
end
********************************************************************
COMMAND OUTPUT:
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
Invalid cursor state
********************************************************************
** Processing....group_name2 - 39 - DBCC_CHECKDB - 1000005.0
** Command: dbcc checkdb ('shims')
********************************************************************
COMMAND OUTPUT:
DBCC results for 'shims'.
DBCC results for 'sysobjects'.
There are 21 rows in 1 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 31 rows in 1 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 256 rows in 4 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 92 rows in 4 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 18 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 15 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 196 rows in 1 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 0 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
CHECKDB found 0 allocation errors and 0 consistency errors in
database 'SHIMS'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
-----Original Message-----
From: Chuck Fox [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 11, 2003 11:19 AM
To: Mitchell, Louise M
Cc: [EMAIL PROTECTED]
Subject: Re: DBD::ODBC - SQL Server - odbc_err_handler - inconsistent
results
Louise,
[EMAIL PROTECTED] wrote:
>All,
>
>Several days ago, I learned how to use the odbc_err_handler subroutine
>provided by DBD::ODBC... in order to capture the 'message' spit out for
>non-DDL/DML commands in a SQL Server, such as 'backup' or 'dbcc'.
>
>The first batch of commands tested were a variety of backup commands
>and then DBCC CHECKDB... and the output was all in the variable, as
>expected...
>
>But then I tested a DBCC INDEXDEFRAG.... in the Query Analyzer it spews
>out some output (not much)... but when I run it thru my process, no
>output is returned...
>
>Anybody have any clues? Code snippet below..
>
>
did you set dbcc traceon( 3604 ) first ? Doesn't appear below. The
traceon ( 3604 ) sends the output to the client, otherwise it falls into
the error log of the server.
>L
>********************
>Snippet for connection:
>
> $dbh_actions = DBI->connect
> ("dbi:ODBC:Driver={SQL
>Server};Server=$action_server;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;
> }
> }
> );
>
>Snippet to execute:
>
>sub just_do_it
>{
> my $command = shift;
> my $sth = $dbh_actions->prepare($command);
> my $rc;
> print "** command in sub: $command \n";
>
> if ( $sth )
> {
> $rc = $sth->execute(); # This is the 'do it'.....
> if ( ! $rc ) # Error
> {
> # Save error messages to email
> $err_msgs{"GROUP: $group_name - ITEM: $item_id"} =
> "Command executed:
>$command\n-------------------------------------\nError
>Message:\n$err_text";
> }
> }
> else
> {
> # Save error messages to email
> $err_msgs{"GROUP: $group_id - ITEM: $item_id"} =
> "Command executed:
>$command\n--------------------------------------\nError
>Message:\n$err_text";
> }
>
> print "\nCOMMAND OUTPUT:\n$err_text\n";
> $err_text = '';
> return $rc;
>
>
HTH,
Chuck
