First of all (in case this is important) I'm using ActiveState's Perl (v5.8.7 built for MSWin32-x86-multi-thread).
I've just emerged from a three hour long debugging session, trying to find the source of an error. I found there was simply no error, but for some reason ADO's connection object was getting the Errors.Count
increased with each printed message in my stored procedure's output.
Consider following Transact SQL code:
CREATE PROCEDURE dbo.My_Sample() AS
BEGIN TRAN my_tran
-- Does something useful
if @@error <> 0 BEGIN
ROLLBACK TRAN my_tran
RAISERROR( 'SP My_Sample failed', 16, 1)
END ELSE BEGIN
COMMIT TRAN my_tran
PRINT 'SP My_Sample succeeded'
END
Now imagine a Perl sub more or less like:
sub execute_SQL {
# $conn is an already opened ADO connection object
# pointing to my SQL Server
# $sql is the T-SQL statement to be executed
my($conn, $sql) = @_;
$conn->Execute($sql);
my $error_collection = $conn->Errors();
my $ecount = $error_collection->Count;
if ($ecount == 0 ) { return 0; }
print "\n" . $ecount . " errors found\n";
print "Executed SQL Code:\n$sql\n\n";
print "Errors while executing:\n";
foreach my $error (in $error_collection){
print "Error: [" . $error->{Number} . "] " . $error->{Description} . "\n";
}
return 1;
}
Somewhere else, in the main Perl code, I'm calling the above sub as:
execute_SQL( $conn, 'EXEC dbo.My_Sample' );
In the end I got it that every PRINT statement causes a new pseudo-error to be appended to the ADO Errors collection. The quick fix I implemented was to change that PRINT in the SP into a SELECT, to bypass this.
The questions I'd like to ask are:
- Is this behaviour normal?
- Is there a way to avoid/bypass it?