Retrieve the error description

In Transact-SQL you can get the error number from @.@.Error, but is there
anything to get the message?
Here's an example that forces a FK error and tries to get a message for it
from the sysmessages table. The message contains all the parameters holders
instead of their values though. Anybody have any ideas?
DECLARE @.intID INT
DECLARE @.intError INT
DECLARE @.strErrorMessage VARCHAR(4000)
UPDATE OrderDetails SET OrderID = 0 WHERE OrderID = 42
SELECT @.intError = @.@.ERROR
IF @.intError <> 0
BEGIN
SELECT @.strErrorMessage = description FROM master.dbo.sysmessages WHERE
error = @.intError
PRINT 'Error Number:' + CAST(@.intError AS VARCHAR)
PRINT 'Error Message:' + @.strErrorMessage
ENDIn SQL 2005, there is the ERROR_MESSAGE() function that can be used inside a
TRY - CATCH block.
In SQL 2000, the only way that I know of to get the actual error message is
by using DBCC OUTPUTBUFFER. It's ugly, but it's the only way I know.
--
"Jack" wrote:

> In Transact-SQL you can get the error number from @.@.Error, but is there
> anything to get the message?
> Here's an example that forces a FK error and tries to get a message for it
> from the sysmessages table. The message contains all the parameters holder
s
> instead of their values though. Anybody have any ideas?
> DECLARE @.intID INT
> DECLARE @.intError INT
> DECLARE @.strErrorMessage VARCHAR(4000)
> UPDATE OrderDetails SET OrderID = 0 WHERE OrderID = 42
> SELECT @.intError = @.@.ERROR
> IF @.intError <> 0
> BEGIN
> SELECT @.strErrorMessage = description FROM master.dbo.sysmessages WHERE
> error = @.intError
> PRINT 'Error Number:' + CAST(@.intError AS VARCHAR)
> PRINT 'Error Message:' + @.strErrorMessage
> END
>
>|||Mark Williams (MarkWilliams@.discussions.microsoft.com) writes:
> In SQL 2000, the only way that I know of to get the actual error message
> is by using DBCC OUTPUTBUFFER. It's ugly, but it's the only way I know.
Yes, that was really ugly! But since the standard answer is "you can't",
this is a leap forward. I will have to try this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm not entirely sure of its usefullness, because DBCC OUTPUTBUFFER doesn't
actually show the error message until after the statement that cause it
completes. You'd have to check @.@.ERROR, dump the contents of DBCC
OUTPUTBUFFER into a temp table, then possibly use a cursor to concatenate
each row into a single text string (whew!).
The question is, will a batch terminate before you get a chance to do
anything usefull with the output.
"Erland Sommarskog" wrote:

> Mark Williams (MarkWilliams@.discussions.microsoft.com) writes:
> Yes, that was really ugly! But since the standard answer is "you can't",
> this is a leap forward. I will have to try this.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>