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