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