Error Checking for Warning Messages

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RMCompute - 02 Jul 2009 13:34 GMT

I have code to check for error messages after each SQL Statement as follows:

Set @iErr = @@Error
If @iErr <> 0
Begin
Set @vErrMess = '#00020 Main Process 020.'
GoTo Error_Handler
End

I would like to test for warning messages such as:

Warning: Null value is eliminated by an aggregate or other SET operation.

The value of @@Error remains zero after this warning message is issued. Is
there another parameter to check which would indicate the error?
Thanks in advance.

Reply to this Message

Erland Sommarskog - 02 Jul 2009 23:05 GMT

> I have code to check for error messages after each SQL Statement as
> follows:
[quoted text clipped - 9 lines]
>
> Warning: Null value is eliminated by an aggregate or other SET operation.

There is no way to capture this message in SQL Server, only on client
level.

By the way, are you still on SQL 2000? If you are on SQL 2005 or later,
you should use TRY-CATCH for your error handling.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

RMCompute - 02 Jul 2009 23:54 GMT

Thank you. Actually, I didn't think there would be a way to trap it; just
wanted to be sure. (Currently there's a bit too many DTS jobs to convert to
2005).

> > I have code to check for error messages after each SQL Statement as
> > follows:
[quoted text clipped - 15 lines]
> By the way, are you still on SQL 2000? If you are on SQL 2005 or later,
> you should use TRY-CATCH for your error handling.

Reply to this Message

--CELKO-- - 03 Jul 2009 01:13 GMT

>> Warning: Null value is eliminated by an aggregate or other SET operation.

The value of @@Error remains zero after this warning message is
issued. <<

As an aside, you have to learn about the ANSI/ISO Standard error
handler model versus the VERY proprietary Sybase/SQL Server local
dialect if you are going to work in RDBMS.

This was a big debate point in ANSI X3H2 with cursors. When is this
exception raised? At DECLARE CURSOR? At OPEN CURSOR? At the FETCH for
the first row with that condition?

The answer is "Yes!!" -- it is implementation defined. Several
products did it differently, so we let them stand as valid. Yet
another reason not to use cursors.

Reply to this Message