Cursed Error Messages

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
Pachydermitis[posted and mailed, please reply in news]

Pachydermitis (dedejavu@.hotmail.com) writes:
> 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.

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
http://www.microsoft.com/sql/techin.../2000/books.asp