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
