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

Martin J. Evans Sun, 21 Feb 2010 01:55:01 -0800

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 forerrors. The while loop over odbc_more_results will stop if a) you runout of results or b) if there is an error. You are getting an error asshown by the 01000 state but you did nothing about it in your errorhandler or by testing odbc_more_results. Check $sth->err afterodbc_more_results returns false and I think you'll find it contains01000 - an error.


Martin

--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

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

Reply via email to