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

--- 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