I am guessing you are using DBD::ODBC - if not, ignore.

I presume the restore database is a procedure? If procedures in MS SQL Server output print messages you need to keep calling odbc_more_results on the statement handle until it returns false. See http://cpansearch.perl.org/src/MJEVANS/DBD-ODBC-1.23/t/20SqlServer.t for an example.

thanks for the reply. Yes, i do use DBD::ODBC Your example shows using odbc_more_results while retrieving data.. (fetch)
my code sniffet is here.


$sql = qq{ 
     RESTORE DATABASE ${dbname}
       FROM disk = '${filename}'
       ${recovery} 
          , MOVE '$datadev_srcname' TO '$datadev_phyname' 
          , MOVE '$logdev_srcname' TO '$logdev_phyname'
    ,replace
    };

}

$sth->execute() || die "Restore failed with this error $DBI::errstr nu
+m: $DBI:err state: $DBI::state\n" ;
[download]

As you can see this is a single command to sql server and i am not sure how do i incorporate your suggestion?
should i change my code to

do{
$sth->execute() || die "Restore failed with this error $DBI::errstr nu
+m: $DBI:err state: $DBI::state\n" ;
}while ($sth->{odbc_more_results});
[download]

I am confused...
thanks

Messages output via print in a procedure are only seen in the DBI error handler so if you want to capture them you need to set that up. There is an example in the 20SqlServer.t test where a procedure is created which prints.

MS SQL Server, by default does not batch up results and the prints are deemed a result (of a sort). I think you need to call execute then loop whilst odbc_more_results is true.

$s->execute;
while($s->odbc_more_results) {
  # your error handler will catch prints 
}
[download]

</code>

i was able to make it work , based on your comment. i got error
Can't locate object method "odbc_more_results" via package "DBI::st"
when i used


while($sth->odbc_more_results) {
  print "Restore outputs $DBI:errstr \n";
}
[download]

googling, i got an option for prepare statement.

my $sth = $db->prepare( $sql, { odbc_exec_direct => 1})
[download]

this seems to keep the connection on (basically to use with temporary table creation on prepare statement, otherwise temp tables are unaccessable)
thanks for your hint, which solved my problem.

That was a typo on my part - it should have been:

while($sth->{odbc_more_results}) {
    ; #
}
[download]

sorry

thanks, now it works with odbc_more_results too.