It may be impossible but I'm not giving up... SQL Guru Needed!

I need a way to catch a SQL error message (not the @@ERROR) for use with a calling procedure... Here's an example:

NOTE: I'm USING SQL2000 (not 2005)


DECLARE @RC int
DECLARE @TXID bigint
DECLARE @LogMode nvarchar(50)
DECLARE @ErrMsg nvarchar(1024)

SET @TXID = 1435                -- Transaction to processes
SET @LogMode = 'Verbose'   `-- Logging mode
SET @ErrMsg = NULL              -- Output Error Message


EXECUTE @RC = [PeopleXpress].[dbo].[prc_Task500_Terminate] @TXID, @LogMode, @ErrMsg OUTPUT

SET @ErrorCode = @@ERROR

Now I can check the @@ERROR object for an error number (or 0)


Here's my question, How do I get the actual error message that goes along with number in @ErrorCode.
     1. One answer is to look it up in sysmessages but that doesn't have the actual message, rather it is the template for the message that I desire.

    2. Another answer is to use DBCC OUTPUTBUFFER to get the error message, after all, this is where ADO.NET goes to get it's friendly sql error messages. The problem is that the DBCC OUTPUTBUFFER needs some post-processing before it can be considered *friendly* -- Do you know how to do this?


Any feed back would make me feel all warm and fuzzy inside. And the correct answer may push me over the cliff of happiness, where I can get some sleep.

Thanks you.



   

You can get the message from dbo.master.sysmessages according to the error code you get from @@ERROR.

P.S.
Next time you should post these kind of questions on the Techoff forum.

Rotem Kirshenbaum wrote:



You can get the message from dbo.master.sysmessages according to the error code you get from @@ERROR.

You're close but no cigar. sysmessages contains template messages but does not have the *actual* message. For example:

In sysmessages, error #1023 reads "Invalid parameter %d specified for %ls."

I need it to say something like "Invalid parameter @UserName specified for spGetUser."

Got any suggestions?

Sorry, that's the best I can come up with
Unless you can upgrade to 2005 (which is probably not an option).

Do you really need to do that in T-SQL?

I thought the errors can be retrieved in SqlException (if using .NET ) or Error collection of ADO Connection object.

Both of them have those %d or so filled for you already.

cheong wrote:

Do you really need to do that in T-SQL?

Yes, it really has to be SQL.

cheong wrote:

I thought the errors can be retrieved in SqlException (if using .NET ) or Error collection of ADO Connection object. Both of them have those %d or so filled for you already.

You're right, ADO gets the *friendly* error messages from the DBCC OUTPUTBUFFER.

What I'm looking for is a way to parse the DBCC OUTPUTBUFFER (It's Hex and has a lot of stuff other than the message) using TSQL.

Call me crazy...

http://www.sommarskog.se/error-handling-I.html#@@error

Search for the section titled "Retrieving the Text of an Error Message."  It documents a stored procedure you can create that parses the OutputBuffer to recreate the message.  I tried it for several scenarios and it worked fine.  Here's one of the tests I used:

DECLARE @err int, @message nvarchar(500)

exec sp_executesql N'exec doesnotexist'

SELECT @err = @@error

IF @err <> 0

Begin

exec showErrorMessage @message output

SELECT @message

end

Perhaps something along these lines ??

Bla bla bla...

BEGIN TRY

   EXECUTE @RC = [PeopleXpress].[dbo].[prc_Task500_Terminate] @TXID, @LogMode, @ErrMsg OUTPUT

END TRY

BEGIN CATCH

    SELECT

        ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

MB wrote:



Perhaps something along these lines ??

Note that he is using SQL2000 in this case, so new features in SQL2005 wouldn't be available.

As for the suggestion by nlondon, surprised that it can actually be done. Good work!

nlondon wrote:

http://www.sommarskog.se/error-handling-I.html#@@error

Search for the section titled "Retrieving the Text of an Error Message."  It documents a stored procedure you can create that parses the OutputBuffer to recreate the message.  I tried it for several scenarios and it worked fine.  Here's one of the tests I used:

DECLARE @err int, @message nvarchar(500)

exec sp_executesql N'exec doesnotexist'

SELECT @err = @@error

IF @err <> 0

Begin

exec showErrorMessage @message output

SELECT @message

end

This is working ok.
But whenever I take the whole block into the transaction it does not produce the desired message. Do you have any idea how to solve that problem and the error message?