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

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