Error Handling SQL 2000

Hello,

Could it be possible to catch into a variable any type of sql server error?

I have an stored procedured that executes as a part of a cycle one stored procedured many times (one for each branch). I need to make the stored procedured to continue even when I have an error in one of the executions (one of the procedures of a branch).

For example, I execute this as a part of a While Statement

Exec @.return_status = @.sp_name @.Link, @.Historia, @.begindate, @.enddate

If @.return_status <> 0
Begin Print ' /* Exito */'
..... update

End
Else
Begin Print '/* Error */'
....
EXEC master.dbo.xp_sendmail
@.recipients = @.mail_recipient,
@.subject = @.mail_subject,
@.message = @.mail_query

End

... But when I get an error like this, the stored just ends the cycle and the stored procedured.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. Authentication failed.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Wish you can help me, thank you

I wish I could help you too. In SqL Server 2000 the only thing you can do is check the value of the error number in the @.@.error system function (used like a variable). So, you may be able to do something like this:

Exec @.return_status = @.sp_name @.Link, @.Historia, @.begindate, @.enddate

If @.@.error <> 0 or @.return_status <> 0

But it could stop the procedure, and it will return the error to the client for handling. In 2005 you have TRY...CATCH, but even that isn't flawless with some errors. For an excellent reference on 2000 error handling, check this article: http://www.sommarskog.se/error-handling-II.html

|||

and don't forget the first in the series:

http://www.sommarskog.se/error-handling-I.html

---

http://www.elsasoft.org

|||Thank you Louis