Re: Error during SQL Server restore fails to propagate back to DBI client on Windows

--- On Sun, 2/21/10, Martin J. Evans <[email protected]> wrote:

> From: Martin J. Evans <[email protected]>
> Subject: Re: Error during SQL Server restore fails to propagate back to DBI client on Windows
> To: "pr" <[email protected]>
> Cc: [email protected]
> Date: Sunday, February 21, 2010, 9:53 AM
> On 19/02/2010 23:22, pr wrote:
> >
> > --- On Fri, 2/19/10, Martin Evans<[email protected]> 
> wrote:
> >
> >    
> >> From: Martin Evans<[email protected]>
> >> Subject: Re: Error during SQL Server restore fails
> to propagate back to DBI client on Windows
> >> To: "pr"<[email protected]>
> >> Cc: [email protected]
> >> Date: Friday, February 19, 2010, 8:41 AM
> >> pr wrote:
> >>      
> >>> --- On Thu, 2/18/10, Martin Evans<[email protected]>
> >>>        
> >> wrote:
> >>      
> >>>        
> >>>> From: Martin Evans<[email protected]>
> >>>> Subject: Re: Error during SQL Server
> restore fails
> >>>>          
> >> to propagate back to DBI client on Windows
> >>      
> >>>> To: "pr"<[email protected]>
> >>>> Cc: [email protected]
> >>>> Date: Thursday, February 18, 2010, 8:51
> AM
> >>>> pr wrote:
> >>>>          
> >>>>> Overview:
> >>>>>
> >>>>> Using perl+DBI+DBD::ODBC on Windows, I
> am
> >>>>>         
>   
> >> connecting
> >>      
> >>>> to a SQL Server 2005 instance and issuing
> a
> >>>>          
> >> "restore
> >>      
> >>>> database [foo] from
> disk='c:\foo-backup.bak'".
> >>>>          
> >>>>> The restore attempt appears to
> succeed, and
> >>>>>         
>   
> >> nothing
> >>      
> >>>> calls my installed error handler.
> >>>>          
> >>>>> However, if I run a trace on the SQL
> Server, I
> >>>>>         
>   
> >> can see
> >>      
> >>>> the restore is throwing an error:
> >>>>          
> >>>>> Error 3224, Severity 16, State 1:
> Cannot
> >>>>>         
>   
> >> create worker
> >>      
> >>>> thread.
> >>>>          
> >>>>> RESTORE DATABASE is terminating
> abnormally.
> >>>>>
> >>>>> If I write the same SQL to a file,
> then
> >>>>>         
>   
> >> execute the
> >>      
> >>>> file via sqlcmd, the restore works as
> >>>>          
> >> expected.  Hence
> >>      
> >>>> I am confident in the SQL command as well
> as the
> >>>>          
> >> backup
> >>      
> >>>> file.
> >>>>          
> >>>>> Other DML-type commands work as
> advertised, as
> >>>>>         
>   
> >> does
> >>      
> >>>> the error handler.  It seems to be
> something
> >>>> specifically about a restore.
> >>>>          
> >>>>> Specifics:
> >>>>>
> >>>>> The version of perl is ActiveState's
> v5.10.0
> >>>>>         
>   
> >> running
> >>      
> >>>> on a 32 bit Windows 2003 server.
> >>>>          
> >>>>> My DSN and connect code looks like:
> >>>>>
> >>>>> my $dsn = 'Driver={SQL Native
> >>>>>         
>   
> >>>>          
> >>
> Client};Server=myServer;Database=master;Trusted_Connection=yes;Encrypt=yes;';
> >>      
> >>>>> my $dbh =
> >>>>>         
>   
> >> DBI->connect("dbi:ODBC:$dsn",'','', {
> >>      
> >>>> RaiseError =>  1, AutoCommit
> =>  1 } );
> >>>>          
> >>>>> $dbh->{HandleError} =
> \&error_handler;
> >>>>>
> >>>>> My error handler looks like:
> >>>>>
> >>>>> sub error_handler {
> >>>>>        my @err =
> @_;
> >>>>>
> >>>>>        print
> "Error handler
> >>>>>         
>   
> >>>> triggered!\n";
> >>>>          
> >>>>>        return 0;
> >>>>> }
> >>>>>
> >>>>> I have tried:
> >>>>> - $dbh->do ( $restore_command )
> >>>>> - $sth = $dbh->prepare(
> $restore_command
> >>>>>         
>   
> >> );
> >>      
> >>>> $sth->execute(); ...
> >>>>          
> >>>>> I've tried with and without
> >>>>>         
>   
> >>>> $dbh->{odbc_exec_direct} = 1 and also
> not
> >>>>          
> >> specifying it.
> >>      
> >>>>> The MDAC version on the machine is
> recent:
> >>>>>         
>   
> >> version
> >>      
> >>>> 2.82.3959.0.
> >>>>          
> >>>>> The version of DBI is 1.604.
> >>>>>
> >>>>> I have tried running a DBI trace at up
> to
> >>>>>         
>   
> >> level 15,
> >>      
> >>>> and there doesn't seem to be anything of
> >>>>          
> >> interest.
> >>      
> >>>>>
> >>>>> Has this been brought up before? 
> Has
> >>>>>         
>   
> >> someone
> >>      
> >>>> encountered it?
> >>>>          
> >>>>> If there are other things I should
> try, I
> >>>>>         
>   
> >> would very
> >>      
> >>>> much appreciate the suggestions!
> >>>>          
> >>>>> I'm completely at a loss where the
> problem is.
> >>>>>         
>   
> >>      
> >>>> Code?  DBD?  DBI?  SQL
> >>>>          
> >> Server?  mdac?
> >>      
> >>>>> Thanks
> >>>>> -rt
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>         
>   
> >>>> Can I see the rest of the code after the
> prepare
> >>>>          
> >> please?
> >>      
> >>>> Preferably a small self contained and
> complete
> >>>>          
> >> example
> >>      
> >>>> would be best.
> >>>>
> >>>> Are you calling odbc_more_results after
> execute
> >>>>          
> >> until it
> >>      
> >>>> returns false?
> >>>>
> >>>> Martin
> >>>> -- 
> >>>> Martin J. Evans
> >>>> Easysoft Limited
> >>>> http://www.easysoft.com
> >>>>
> >>>>          
> >>> Hi Martin,
> >>>
> >>> Thank you for your reply.
> >>>
> >>> I'm depending on DBI's do() method:
> >>>
> >>> my
> $dbh=DBI->connect("dbi:ODBC:$dsn",'','');
> >>> $dbh->{HandleError} = sub { print "error
> >>>        
> >> occurred\n"; };
> >>      
> >>> $sql = 'restore database [foo] from
> >>>        
> >> disk=\'c:\\mybackup.bak\' with replace';
> >>      
> >>> $dbh->do( $sql );
> >>>
> >>>
> >>>
> >>> I've also tried:
> >>>
> >>> my
> $dbh=DBI->connect("dbi:ODBC:$dsn",'','');
> >>> $dbh->{HandleError} = sub { print "error
> >>>        
> >> occurred\n"; };
> >>      
> >>> $sql = 'restore database [foo] from
> >>>        
> >> disk=\'c:\\mybackup.bak\' with my $sth =
> $dbh->prepare(
> >> $sql );
> >>      
> >>> my $dbh->execute();
> >>> while ( my @junk = $sth->fetchrow_array() )
> {
> >>>      # do nothing
> >>> }
> >>>
> >>>
> >>> In both cases, the error handler is not
> triggered, and
> >>>        
> >> all appears well on the client in.  However,
> a trace
> >> using SQLProfiler shows Msg 3224 and that the
> backup
> >> terminates abnormally.
> >>      
> >>> I've been able to confirm this on two systems
> running
> >>>        
> >> the same versions of the software.
> >>      
> >>>
> >>> I would expect either approach to work?
> >>>
> >>> Regards,
> >>> -rt
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>        
> >> When you call procedures in MS SQL Server you
> often need to
> >> use
> >> prepare/execute/odbc_more_results because SQL
> Server
> >> batches up results
> >> and anything printed in the procedure can count as
> a
> >> result. I would try
> >> this:
> >>
> >> $sth = prepare(restore....)
> >> $sth->execute
> >> do {
> >>              my
> @row;
> >>             
> while (@row =
> >> $sth->fetchrow_array()) {
> >>         
>    
> >>     # do stuff here
> >>              }
> >>           }
> while
> >> ($sth->{odbc_more_results});
> >>
> >>
> >> There is an example in t/20SqlServer.t that comes
> with
> >> DBD::ODBC - you
> >> can find it by browsing the source on CPAN.
> >>
> >> Martin
> >> -- 
> >> Martin J. Evans
> >> Easysoft Limited
> >> http://www.easysoft.com
> >>
> >>      
> >
> >
> > Hi Martin,
> >
> > Once again, thank you for your time and patience.
> >
> > The suggestion to use $sth->{odbc_more_results}
> improved the situation, but there still seems to be a major
> issue.
> >
> >
> > What works:
> >
> > my $dbh = DBI->connect("dbi:ODBC:$dsn", '', '');
> > $dbh->{HandleError} = \&error_handler;
> > $dbh->{HandleSetErr} = \&set_error_handler;
> > $dbh->{odbc_exec_direct} = 1;
> > my $sql = qq| restore database [foo] from
> disk='l:\\mssql.1\\mssql\\backup\\full\\foo.bak' WITH
> REPLACE |;
> > $sth = $dbh->prepare($sql);
> > $sth->execute();
> > do {
> >     print "RESULT SET:\n";
> >     while ( my @row =
> $sth->fetchrow_array() ) {
> >         print
> "\tfetchrow_array() should be empty\n";
> >     }
> > } while ( $sth->{odbc_more_results} );
> >
> >
> > What does not work:
> >
> > Remove the call to set dbc_exec_direct to 1.
> >
> > In this case, I see in a SQLProfiler trace that the
> backup is prepared via a call to sp_prepexec (as would be
> expected).  However, the trace shows the server
> throwing Msg 3224 Severity 16 State 1 - cannot spawn worker
> thread again.
> >
> > The main worry I have is this error does not propagate
> back to the client at all.
> >
> > Here is the code for the error handler and set err
> handler:
> >
> > sub error_handler {
> >     my @err = @_;
> >     
> >     for ( my $i = 0; $i< 
> scalar( @err ); $i++ ) {
> >         if ( ! defined
> $err[$i] ) {
> >        
>     $err[$i] = '(null)';
> >         }
> >         print "ERROR
> HANDLER: $i: $err[$i]\n";
> >     }
> > }
> >
> > sub set_error_handler {
> >     my @err = @_;
> >     
> >     for ( my $i = 0; $i< 
> scalar( @err ); $i++ ) {
> >         if ( ! defined
> $err[$i] ) {
> >        
>     $err[$i] = '(null)';
> >         }
> >         print "SET ERROR
> HANDLER: $i: $err[$i]\n";
> >     }
> > }
> >
> >
> > When I run the script in the failure case (i.e. not
> setting odbc_exec_direct), I see exactly this:
> >
> > SET ERROR HANDLER: 0: DBI::st=HASH(0x1a4d614)
> > SET ERROR HANDLER: 1:
> > SET ERROR HANDLER: 2: [Microsoft][SQL Native
> Client][SQL Server]Processed 160 pages for database 'foo',
> file 'foo' on file 1. (SQL-01000)
> > SET ERROR HANDLER: 3: 01000
> > SET ERROR HANDLER: 4: (null)
> > RESULT SET:
> >
> > Hence there's every reason to believe from the client
> point of view this would be a success.
> >
> >
> > The exact versions of the DBI and DBD::ODBC module
> (updated from ActiveState) are:
> >
> > 1.609 DBI
> > 1.23 DBD::ODBC
> >
> >
> > Any other suggestions?  Would it be helpful to
> bundle up an exact set of scripts to create, backup and run
> a restore to see if anyone else can reproduce this? 
> I've been able to reproduce this on other servers with a
> dummy/test setup.
> >
> > Regards,
> > -rt
> >
> >    
> 
> The example I sent was just pseudo code. You still need to
> check for 
> errors. The while loop over odbc_more_results will stop if
> a) you run 
> out of results or b) if there is an error. You are getting
> an error as 
> shown by the 01000 state but you did nothing about it in
> your error 
> handler or by testing odbc_more_results. Check $sth->err
> after 
> odbc_more_results returns false and I think you'll find it
> contains 
> 01000 - an error.
> 
> Martin
> 
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
> 
> 



I believe I am properly checking for errors, but I am not seeing one.

Can I correctly assert the following:

Assuming I have a valid connection to a database in $dbh using DBD::ODBC, shouldn't I only ever need to install an error handler routine via $dbh->{HandleError} = \&my_handler in order to trap any error?  

Specifically, I should not need a HandleSetErr, and I should not need to check error handling inline via $sth->err and the like.  This is true even with multiple result sets and the need to check for more results via $sth->{odbc_more_results}.



Assuming my understanding above is correct: I am not seeing any error message for the restore when odbc_exec_direct is off, though I do see an error thrown by the SQL Server via a SQLProfiler trace.  I do not see the error on an installed HandleError error handler, nor do I see an error even if I also do inline error checking after ANY call on $sth, specifically including after $sth->{odbc_more_results} returns false.


# Note in this example I never touch/install anything to $dbh->{HandleSetErr}
$dbh->{HandleError} = sub { croak "Database error: $_[2]\n"; };
$dbh->{odbc_exec_direct} = 0;
$sql = 'restore database [bar] from disk=\'e:\\bar.bak\'';
$sth = $dbh->prepare($sql);
$sth->execute();
do {
	print "RESULT SET:\n";
	if ( $sth->err ) {
		croak 'Database error: ' . $sth->errstr . "\n";
	}
	while ( my @row = $sth->fetchrow_array() ) {
		print "   fetchrow_array() should be empty, but still, here we are\n";
	}
	if ( $sth->err ) {
		croak 'Database error: ' . $sth->errstr . "\n";
	}
} while ( $sth->{odbc_more_results} );

if ( $sth->err ) {
	croak 'Database error: ' . $sth->errstr . "\n";
}


The above does not croak().  It appears to work; the SQL Server trace shows Msg 3224.

As before, e:\bar.bak is a valid database backup which restores OK via e.g. sqlcmd, or if odbc_direct_exec = 1.  The concern is not that the restore fails with odbc_direct_exec=0; it's not seeing an error at all in this case.

  
Thank you once again,
-rt