--- On Fri, 2/19/10, pr <[email protected]> wrote:
> From: pr <[email protected]> > Subject: Re: Error during SQL Server restore fails to propagate back to DBI > client on Windows > To: "Martin Evans" <[email protected]> > Cc: [email protected] > Date: Friday, February 19, 2010, 11:22 PM > > > --- 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 > > One other thing I should add: It seems peculiar that I can only get the first 'Processed x pages for database ....' message, but none others. This is true even when using odbc_more_results to ensure all result sets get processed, and true whether or not odbc_exec_direct is 1 or 0. It seems these messages can only be caught via the HandleSetErr handler, and not the HandleError handler. I am wondering if this is related. It would seem if in SQL Server I do: create database foo; backup database foo to disk='e:\foo.bak' I should then be able to issue via a DBI connection using DBD::ODBC the command: restore database foo from disk='e:\foo.bak' with replace And see all 3 status messages that I would see if I issued this command at a sqlcmd prompt: 1> restore database bar from disk='e:\bar.bak' with replace 2> go Processed 152 pages for database 'bar', file 'bar' on file 1. Processed 1 pages for database 'bar', file 'bar_log' on file 1. RESTORE DATABASE successfully processed 153 pages in 0.056 seconds (22.381 MB/sec). I don't, though. I see only that first 'Processed 152 pages...' message, in spite of using odbc_more results as follows: $dbh=DBI->connect("dbi:ODBC:$dsn",'',''); $dbh->{odbc_exec_direct} = 1; $dbh->{HandleSetErr} = sub { print "$_[2]\n"; }; $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 " fetchrow_array() should be empty, but still, here we are\n"; } } while ( $sth->{odbc_more_results} ); I see: [Microsoft][SQL Native Client][SQL Server]Processed 160 pages for database 'foo', file 'foo' on file 1. (SQL-01000) RESULT SET: Thank you again for any suggestions or specific RTFM pointers. -rt
