Trapping error messages

[Shervin Shapourian]

Hi everybody,

I need to trap error messages in my stored procedures and log them. I can
use @@ERROR global variable to get the error code and look it up in
sysmessages table to get the description. Then using xp_logevent I log the
error.
The problem is this description needs to be formatted. For example if I try
to insert NULL into a column which is not nullable, I'll get error #515. The
description of error #515 in sysmessages is:

Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column
does not allow nulls. %ls fails.

Is there a way to get the formatted message? What is the best approach to
trap errors, filter them, add some additional information to the message and
send it to server's event logger?

TIA,
Shervin

[Simon Hayes]

"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message
news:vp************@corp.supernews.com...

Hi everybody,

I need to trap error messages in my stored procedures and log them. I can
use @@ERROR global variable to get the error code and look it up in
sysmessages table to get the description. Then using xp_logevent I log the
error.
The problem is this description needs to be formatted. For example if I try to insert NULL into a column which is not nullable, I'll get error #515. The description of error #515 in sysmessages is:

Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column
does not allow nulls. %ls fails.

Is there a way to get the formatted message? What is the best approach to
trap errors, filter them, add some additional information to the message and send it to server's event logger?

TIA,
Shervin

The text of the message can't be trapped in TSQL, only on the client. There
is a lot of detailed information on this here:

http://www.algonet.se/~sommar/error-...I.html#@@error

Simon

[Shervin Shapourian]

Simon,

I'm looking for a solution to log all errors on server side, either in
server's event log or in a table. I'm gonna read the article you sent me.

Thanks a lot,
Shervin
"Simon Hayes" <sq*@hayes.ch> wrote in message
news:3f********@news.bluewin.ch...

The text of the message can't be trapped in TSQL, only on the client. There is a lot of detailed information on this here:

http://www.algonet.se/~sommar/error-...I.html#@@error

Simon

[Erland Sommarskog]

Shervin Shapourian (Sh**********@hotmail.com) writes:

I'm looking for a solution to log all errors on server side, either in
server's event log or in a table. I'm gonna read the article you sent me.

Alas, this is not possible. You need to have a co-operating client.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jason]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn********************@127.0.0.1>...

Shervin Shapourian (Sh**********@hotmail.com) writes:

I'm looking for a solution to log all errors on server side, either in
server's event log or in a table. I'm gonna read the article you sent me.

Alas, this is not possible. You need to have a co-operating client.

I have been taking the approach that whatever could be done on the
server SHOULD be done on the server. (i.e. Use the scheduler in SQL
Agent to kick off jobs, have the jobs write out to tables for logging
since errors cannot REALLY be captured, and so on...)

Is this wrong? Should I be using a third-party schedules and sending
ISQL (I think that is the command) or regular ADO type scripts to kick
off stored procedures? Is there a policy or concept of when to
separate jobs from server?

[Shervin Shapourian]

Thanks Erland, I'm afraid I have to do that :-)

Shervin

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn********************@127.0.0.1...

Shervin Shapourian (Sh**********@hotmail.com) writes:

I'm looking for a solution to log all errors on server side, either in
server's event log or in a table. I'm gonna read the article you sent

me.
Alas, this is not possible. You need to have a co-operating client.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Bell]

Hi

You are not going to be able to trap the error on the server itself. If you
trap it on the client then you can write it to a log table, but then if the
error is to do with the database being corrupted and unwritable or similar
you are stuck.

John

"Jason" <Ja*******@hotmail.com> wrote in message
news:f0**************************@posting.google.c om...

Erland Sommarskog <so****@algonet.se> wrote in message

news:<Xn********************@127.0.0.1>...

Shervin Shapourian (Sh**********@hotmail.com) writes:

I'm looking for a solution to log all errors on server side, either in
server's event log or in a table. I'm gonna read the article you sent

me.
Alas, this is not possible. You need to have a co-operating client.

I have been taking the approach that whatever could be done on the
server SHOULD be done on the server. (i.e. Use the scheduler in SQL
Agent to kick off jobs, have the jobs write out to tables for logging
since errors cannot REALLY be captured, and so on...)

Is this wrong? Should I be using a third-party schedules and sending
ISQL (I think that is the command) or regular ADO type scripts to kick
off stored procedures? Is there a policy or concept of when to
separate jobs from server?