Retrieving the error message

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
ENDPlease ignore this post. I triple-posted it by accident.

Retrieving error messages

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 the message from
sysmessages. As you will see the error message doesn't contain the
parameter values. Anybody have any ideas? I'm still using SQL Server 2000,
so I can't use the new 2005 error functions.
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
ENDPlease ignore this post. I triple-posted it by accident.

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
>