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

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