471,108 Members | 1,254 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

Trapping error messages

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
Jul 20 '05 #1
6 5443

"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
Jul 20 '05 #2
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

Jul 20 '05 #3
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
Jul 20 '05 #4
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?
Jul 20 '05 #5
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

Jul 20 '05 #6
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?

Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Pete | last post: by
6 posts views Thread by Tony Williams | last post: by
13 posts views Thread by Thelma Lubkin | last post: by
2 posts views Thread by Captain Nemo | last post: by
9 posts views Thread by 47computers | last post: by
4 posts views Thread by franc sutherland | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.