Missing error/exception information

[Mark de Waal]

Hello all,

When I raise more than one error in a stored procedure, like this:

Code: Select all

CREATE PROCEDURE dbo.MultipleErrors
AS
BEGIN
   RAISERROR('First error', 16, 1)
   RAISERROR('Second error', 16, 1)
   RAISERROR('Third error', 16, 1)
END

and run this stored procedure in Management Studio, I see this:

Code: Select all

Msg 50000, Level 16, State 1, Procedure MultipleErrors, Line 4
First error
Msg 50000, Level 16, State 1, Procedure MultipleErrors, Line 5
Second error
Msg 50000, Level 16, State 1, Procedure MultipleErrors, Line 6
Third error

When I call this stored procedure from Delphi 2010 with DevArt DbxSda 5.01 I get only this in the TDBXError.Message:

Code: Select all

SQL Error Code: 50000
First Error

Questions:
- Where is the extra error information like Level, State, Procedure name and line?
- Where are the "Second error" and "Third" Error"?

Is this a DBExpress problem or something DevArt can fix?

Note: This example looks a bit silly, but in a pre-DBExpress era I have used multiple errors with nested procedure calls. Every calling procedure raised an error when the called procedure returned with a value 0 to indicate that there was an error. There were no try/catch blocks used. Eventually, the Delphi application would get an exception with all error messages combined. With this super-exception message you had a kind of "call stack" which made it easier to track problems.

Edit:
While testing this issue I noticed something else. When I PRINT text in the stored procedure, like this:

Code: Select all

CREATE PROCEDURE dbo.MultipleErrors
AS
BEGIN
   PRINT 'First Print'
   PRINT 'Second Print'
   PRINT 'Third Print'
   RAISERROR('First error', 16, 1)
   RAISERROR('Second error', 16, 1)
   RAISERROR('Third error', 16, 1)
END;

and run it in Management Studio all is as expected:

Code: Select all

First Print
Second Print
Third Print
Msg 50000, Level 16, State 1, Procedure MultipleErrors, Line 7
First error
Msg 50000, Level 16, State 1, Procedure MultipleErrors, Line 8
Second error
Msg 50000, Level 16, State 1, Procedure MultipleErrors, Line 9
Third error

But when I call it from Delphi the exception.message contains:

Code: Select all

SQL Error Code: 0
First Error
Third Print
Second Print
First Print

Questions:
- Why are the PRINTs in the exception?
- Why is the SQL Error Code 0 in stead of 50000?
- Why are the PRINTs and Error in reversed order?

Again, this could be a problem of DBExpress. When I use the Embarcadero drivers (D2010 - XE2) I get this:

Code: Select all

SQL State: 42000, SQL Error Code 50000
First Error
SQL State: 01000, SQL Error Code: 0
Third Print
SQL State: 01000, SQL Error Code: 0
Second Print
SQL State: 01000, SQL Error Code: 0
First Print

- Only one exception, not three
- Prints are a part of the exception, but have a special sql state
- Prints and exceptions are in reversed order
- But exception still has it's SQL Error Code 50000

Thanks in advance for your time and any solutions/tips you can offer me

[AndreyZ]

Hello,

It's impossible to get all three errors from the TDBXError exception class that is used in the dbExpress exception mechanism. I cannot reproduce the problem when the PRINT statements are in the exception message. Please specify the code you are using to get the exception message. Also, please specify the exact version of SQL Server you are working with. For this, you can execute the "select @@version" statement in Microsoft SQL Server Management Studio.

[Mark de Waal]

Hello AndreyZ,

I have a local MSSQL Server on my workstation with this version:

Code: Select all

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) 

but have the same problem with a enterprise edition MSSQL server:

Code: Select all

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

The Delphi code I use is very simple:
- Create new VCL Forms application
- Add TSQLConnection component to Form1 and configure for DevArt driver and my database
- Add TSQLStoredProc component and connect to TSQLConnection. Set schema and procedurename (to 'dbo' and 'MultipleErrors' from the example above)
- Add TButton and implement click to: SQLStoredProc1.ExecProc

I don't know if I can attach a file, so here is the pas and dfm of my form:

Code: Select all

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, WideStrings, DBXDevartSQLServer, FMTBcd, StdCtrls, DB, SqlExpr;

type
  TForm1 = class(TForm)
    SQLConnection1: TSQLConnection;
    SQLStoredProc1: TSQLStoredProc;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLStoredProc1.ExecProc;
end;

end.

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 337
  ClientWidth = 635
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 72
    Top = 128
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 0
    OnClick = Button1Click
  end
  object SQLConnection1: TSQLConnection
    DriverName = 'DevartSQLServer'
    GetDriverFunc = 'getSQLDriverSQLServer'
    LibraryName = 'dbexpsda40.dll'
    LoginPrompt = False
    Params.Strings = (
      'HOSTNAME=(local)'
      'DATABASE='
      'USER_NAME=sa'
      'PASSWORD=')
    VendorLib = 'sqlncli.dll'
    Left = 56
    Top = 56
  end
  object SQLStoredProc1: TSQLStoredProc
    SchemaName = 'dbo'
    MaxBlobSize = -1
    Params = 
    SQLConnection = SQLConnection1
    StoredProcName = 'MultipleErrors'
    Left = 160
    Top = 56
  end
end

Note: Usually I use sqlncli.dll, but as a test I changed it back to the default sqloledb.dll and this does change the behaviour! Now the exception has this message:

Code: Select all

SQL Error Code: 0
Third error
Second error
First error
Third Print
Second Print
First Print

Although all three errors are now visible, the other problems remain:
- Prints in exception
- Reversed order
- SQL Error code 0 in stead of 50000

Thanks for any help you can give me!

[AndreyZ]

I still cannot reproduce the problem. I tried executing your stored procedure using SQL Server 2008 R2 10.50.1600.1 Developer Edition and Delphi versions from 2010 to XE2. The exception message contained only the "First error" message independently of the provider used (OLEDB or SQL Native Client). For further investigation, we need access to your SQL Server (only rights to execute the MultipleErrors stored procedure). If you can provide such access, please send the server IP, login, password, and database name to andreyz*devart*com.

[Mark de Waal]

I am sorry but company policy does not allow remote access to our (database) servers or workstations.

The problem is not as straight forward as I thought, so I did the following:

- I installed Windows Server 2008 R2 with Service Pack 1 (x64) in a new VMware session
- I installed Microsoft SQL Server 2008 R2 x64 Enterprise Edition with minimal options (engine services, client tools connectivity and management tools-complete)
- I created a new database and in it the stored procedure MultipleErrors from the original post above (the one including the print statements)
- I installed the dbxsda 5.01 trial version that I downloaded some time ago from your website
- I ran my delphi application that calls stored procedure MultipleErrors

The result was only the "First Error", like you reported.

When checking your website I discovered the 5.02 trial from december 16th, so I installed this. Unfortunately this expired the trial period?!, so I uninstalled the trial and copied the dbexpsda40.dll that we use in our development project. I think this is also based on 5.01 (we have source) but it also has the SQL error code in the error text, like 5.02 is supposed to have?!

Anyway, I experimented with my code and finaly I think I found the problem. When I add a "SET NOCOUNT ON" instruction to the stored procedure, I get the described behaviour:

Code: Select all

ALTER PROCEDURE dbo.MultipleErrors 
AS 
BEGIN 
   SET NOCOUNT ON
   PRINT 'First Print' 
   PRINT 'Second Print' 
   PRINT 'Third Print' 
   RAISERROR('First error', 16, 1) 
   RAISERROR('Second error', 16, 1) 
   RAISERROR('Third error', 16, 1) 
END;

Now the prints are shown in the error when my testapp runs with sqlncli.dll and all errors/prints are shown when it is using sqloledb.dll

Can you confirm this?

[AndreyZ]

Yes, you are right. Adding the "SET NOCOUNT ON" line changes the error message. OLEDB, SQL Native Client, and SQL Native Client 10 providers return different messages.
OLEDB (sqloledb.dll):
Third error
Second error
First error
Third Print
Second Print
First Print

SQL Native Client (sqlncli.dll):
First error
Third Print
Second Print
First Print

SQL Native Client 10 (sqlncli10.dll):
Third error
Second error
First error
Third Print
Second Print
First Print

We cannot influence the order of particular lines in these messages, they are returned this way by providers.
We are investigating why our driver doesn't return the correct SQL error code. As soon as we have any results, we will notify you.

[Mark de Waal]

I am not on your design team, but I would say that the prints should not be a part of the exception(message)

And also I would like to be able to get all additional raiserror information like errornumber, severity, state, stored procedure name, linenumber and error text. If the exception.message is the only way to "transport" this information from the DevArt driver to the Delphi application, because of dbExpress in the middle, maybe you could make the message customizable/formatable?

And if it is in any way possible, all error messages should be reported and they should be in the correct order

Thanks again for your effort!

[Mark de Waal]

This is what I read in the online help from microsft http://msdn.microsoft.com/en-us/library/ms178592.aspx and http://msdn.microsoft.com/en-us/library/ms177497.aspx

... When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:
* Outside the scope of any TRY block.
* With a severity of 10 or lower in a TRY block.
* With a severity of 20 or higher that terminates the database connection.


I think that PRINTs (error 0, severity 0) should not be a part of the exception, just like all "errors" with severity <= 10. Maybe there is another way to give these "information/warning"-texts to the calling (Delphi) application?

[AndreyZ]

dbExpress driver for SQL Server returns all error messages that SQL Server returns. As you can see Microsoft SQL Server Management Studio shows messages from the PRINT statements.
We will investigate your questions about the order of errors and the full error information in error messages.