views:

704

answers:

3

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?
+4  A: 

This is to be expected as it's what ADO does and the Win32::ADO is quite a thin layer above it.

ref: knowledge base note that the RAISERROR and PRINT statements are returned through the ADO errors collection

Richard Harrison
Thanks! This seems like the answer, however the KB article says that "at least one RAISERROR statement is required in the stored procedure to retrieve a PRINT statement with ADO through the Errors collection" - does that mean a RaisError anywhere in the SP, even if not called on success (as here)?
Joe Pineda
The article also says "In addition, to work around the issue of specifying no RAISERROR statements and a combination of PRINT statements, default RAISERROR statements are generated in order to provide a context for returning the PRINT statement via ADO."maybe try a RAISEERROR with a value of 1...
Richard Harrison
+1  A: 

OK, after a lot of testing and reading, I came to found it explained in the BOLs' article "Using PRINT" (my emphasis):

The PRINT statement is used to return messages to applications. PRINT takes either a character or Unicode string expression as a parameter and returns the string as a message to the application. The message is returned as an informational error to applications using the SQLClient namespace or the ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC) application programming interfaces (APIs). SQLSTATE is set to 01000, the native error is set to 0, and the error message string is set to the character string specified in the PRINT statement. The string is returned to the message handler callback function in DB-Library applications.

Armed with this knowledge I adapted this VB6 from this DevX article until I got this:

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
  # Returns 0 if no error found, 1 otherwise
  my($conn, $sql) = @_;
  $conn->Execute($sql);
  my $error_collection = $conn->Errors();
  my $ecount = $error_collection->Count;
  if ($ecount == 0 ) { return 0; }

  my ($is_message, $real_error_found);
  foreach my $error (in $error_collection){
    $is_message = ($error->{SQLState} eq "01000" && $error->{NativeError}==0);
    $real_error_found=1 unless $is_message;

    if( $is_message) {
      print "Message # " . $error->{Number}
      . "\n Text: " . $error->{Description} ."\n";
    } else {
      print "Error # " . $error->{Number}
      . "\n Description: " . $error->{Description}
      . "\nSource: " . $error->{Source} . "\n";
    }
  }

  print $message_to_print;
  return $real_error_found;
}

So now my Perl sub correctly sorts out real errors (emitted from SQL Server via a RaisError) and a common message outputted via "PRINT".

Thanks to Richard Harrison for his answer which lead me to the way of success.

Joe Pineda
accept it - the whole point here is to share our problems and solutions, quoting references as appropriate.
Richard Harrison
Sorry, by "it" are you referring to my own answer, or the previous one that sent me on the right path to success?
Joe Pineda
I just noticed you cannot accept your own answers (or at least I don't have enough karma to do it) (LOL)
Joe Pineda
A: 

Hello,

I did something similar to this in Sybase (sp_showplan ,null,null,null). But the problem is the Errors are not sequential. So, its printing the output in jumbled fashion compared to a normal isql session. Anyone know a fix for this?

Vishi