sql server (alternate): Hi everyone,
How do I get the error message?
I have a very long sproc that needs to be done in one transaction. I
have an error happening somewhere in the middle, but with a low enough
severity it doesn't terminate the procedure. To make sure I don't
miss any errors, I am storing @@error after every statement:
If @Error<=@@error Set @Error=@@error
that way at the end I can say if @error<>0 rollback trans.
How do I get the error message? I have the number, and what I get
from sysmessages has the wildcards in it %d and so on.
Also , I can't use Xact_Abort, the web user permissions don't allow
it.

Or better yet is there a better way to do this? Sql has
@@total_errors - since the server was started, how about since the
transaction or the sproc was started.
Thanks a ton

[posted and mailed, please reply in news]

Pachydermitis (dedejavu@hotmail.com) writes:
[quoted text, click to view]

There is no way to get the text of the error message in SQL. You must
catch it on client level.

If your code is as above, there is a serious problem in your error
handling. @@error is set after each statement, so you will always
set @error to 0 above.

I have an article on my web site about error handling, that you may
find useful. http://www.sommarskog.se/error-handling-I.html.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at