I'm having a problem where the error string from a raiserror (inside a
stored procedure) is disappearing.  I've reduced it to the fact that it
only occurs if there is some part of the stored procedure that returns
rows (0 or more).

Here is a code snippet that illustrates the problem:



use DBI;
use DBD::ODBC;


my $dbh = DBI->connect("my_sqlserver_database","myuser","mypass");


$sql = "create procedure test_raiserror1
        as begin
           raiserror('my error',16,1) with nowait
        end";
$dbh->do($sql) || die $DBD::errstr;

$sql = "create procedure test_raiserror2
        as begin
           select (1)
           raiserror('my error',16,1) with nowait
        end";
$dbh->do($sql) || die $DBD::errstr;

print "created\n\n";


#this bit correctly returns "my error" into dbi::errstr
$sql = "test_raiserror1";
print "test_raiserror1:\n";
$rows = $dbh->do($sql);
print "rows: $rows\n";
print "errstr: $DBI::errstr\n\n";

#this one does not
$sql = "test_raiserror2";
print "test_raiserror2:\n";
$rows = $dbh->do($sql);
print "rows: $rows\n";
print "errstr: $DBI::errstr\n\n";


$sql = "drop procedure test_raiserror1";
$dbh->do($sql) || die $DBD::errstr;

$sql = "drop procedure test_raiserror2";
$dbh->do($sql) || die $DBD::errstr;


print "dropped\n\n";





Note how the first one returns the error, while the second one simply
returns -1.

I think it may have to do with the string returned from sql server.
Here is the difference in the return string when they are executed in ms
sql query analyzer:


test1:
Server: Msg 50000, Level 16, State 1, Procedure test_raiserror1, Line 4
my error


test2:
(1 row(s) affected)

Server: Msg 50000, Level 16, State 1, Procedure test_raiserror1, Line 4
my error



Does anyone know how I can get at the error string?

Thanks,
Adam Mitchell

Reply via email to