Saving Error Message to Variable (ala @@ERROR)

[BradJones]

I would like to save the error message that SQL-Server generates when an error occurs.
I am able to select @@ERROR to get the the error number and I am then able to look up the generic error message from the sysmessages table. However, the sysmessages is generic and does not have, for example, the name of the table that was not found.

See code below:
select * from NON_EXISTANT
go
select * from master.dbo.sysmessages
where error = (select @@error)

Results in this:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'NON_EXISTANT'.

error severity dlevel description
----------------------------------------------------
208 16 0 Invalid object name '%.*ls'.

As you can see the if I saved the data from the SYSMESSAGES table to a variable, I would not get 'NON_EXISTANT' but rather '%.*ls'.

How can I access the error message :
"Invalid object name 'NON_EXISTANT'." in T-SQL.

Thanks for any help.
Brad
[sig][/sig]

 

[jnicho02]

You've got to put any @@ values into variables immediately after a statement if you want to access them...

[tt]
declare int @err
select * from NON_EXISTANT
select @err = @@error
select * from master.dbo.sysmessages
[tab]where error = @err
go
[/tt]

...or something like that. My syntax is never perfect and I don't have a SQL Server where I am at the moment to try it on.. [sig]<p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br> [/sig]

 

[BradJones]

Jnicho2,

Thanks for the info and your help.

However, I was not having a problem storing the @@error (which is the numeric error code):
&quot;208&quot;

Nor was I having a problem selecting the generic error from the sysmessages table (which your query would return):
&quot;Invalid object name '%.*ls'.&quot;

What I was unable to do was select the orginal error message that was genereated which includes the table name:
&quot;Invalid object name 'NON_EXISTANT'.&quot;

I would have thought there was a system variable (like @@ERROR) that stores the actual message generated but I am beginning to think there is none.

Thanks,
Brad

[sig][/sig]