--- On Sun, 2/21/10, Martin J. Evans <[email protected]> wrote:
> From: Martin J. 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: Sunday, February 21, 2010, 9:53 AM > On 19/02/2010 23:22, pr wrote: > > > > --- 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 > > > > > > The example I sent was just pseudo code. You still need to > check for > errors. The while loop over odbc_more_results will stop if > a) you run > out of results or b) if there is an error. You are getting > an error as > shown by the 01000 state but you did nothing about it in > your error > handler or by testing odbc_more_results. Check $sth->err > after > odbc_more_results returns false and I think you'll find it > contains > 01000 - an error. > > Martin > > -- > Martin J. Evans > Easysoft Limited > http://www.easysoft.com > > I believe I am properly checking for errors, but I am not seeing one. Can I correctly assert the following: Assuming I have a valid connection to a database in $dbh using DBD::ODBC, shouldn't I only ever need to install an error handler routine via $dbh->{HandleError} = \&my_handler in order to trap any error? Specifically, I should not need a HandleSetErr, and I should not need to check error handling inline via $sth->err and the like. This is true even with multiple result sets and the need to check for more results via $sth->{odbc_more_results}. Assuming my understanding above is correct: I am not seeing any error message for the restore when odbc_exec_direct is off, though I do see an error thrown by the SQL Server via a SQLProfiler trace. I do not see the error on an installed HandleError error handler, nor do I see an error even if I also do inline error checking after ANY call on $sth, specifically including after $sth->{odbc_more_results} returns false. # Note in this example I never touch/install anything to $dbh->{HandleSetErr} $dbh->{HandleError} = sub { croak "Database error: $_[2]\n"; }; $dbh->{odbc_exec_direct} = 0; $sql = 'restore database [bar] from disk=\'e:\\bar.bak\''; $sth = $dbh->prepare($sql); $sth->execute(); do { print "RESULT SET:\n"; if ( $sth->err ) { croak 'Database error: ' . $sth->errstr . "\n"; } while ( my @row = $sth->fetchrow_array() ) { print " fetchrow_array() should be empty, but still, here we are\n"; } if ( $sth->err ) { croak 'Database error: ' . $sth->errstr . "\n"; } } while ( $sth->{odbc_more_results} ); if ( $sth->err ) { croak 'Database error: ' . $sth->errstr . "\n"; } The above does not croak(). It appears to work; the SQL Server trace shows Msg 3224. As before, e:\bar.bak is a valid database backup which restores OK via e.g. sqlcmd, or if odbc_direct_exec = 1. The concern is not that the restore fails with odbc_direct_exec=0; it's not seeing an error at all in this case. Thank you once again, -rt
