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]

Re: Trapping error messages


"Shervin Shapourian" <ShShapourian@h otmail.com> wrote in message
news:vpj76ijcj9 4g57@corp.super news.com...[color=blue]
> 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[/color]
try[color=blue]
> to insert NULL into a column which is not nullable, I'll get error #515.[/color]
The[color=blue]
> 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[/color]
and[color=blue]
> send it to server's event logger?
>
> TIA,
> Shervin
>
>[/color]

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-handling-I.html#@@error

Simon

[Shervin Shapourian]

Re: Trapping error messages

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" <sql@hayes.ch > wrote in message
news:3f9a6a50_3 @news.bluewin.c h...[color=blue]
>
> The text of the message can't be trapped in TSQL, only on the client.[/color]
There[color=blue]
> is a lot of detailed information on this here:
>
> http://www.algonet.se/~sommar/error-...I.html#@@error
>
> Simon
>
>[/color]

[Erland Sommarskog]

Re: Trapping error messages

Shervin Shapourian (ShShapourian@h otmail.com) writes:[color=blue]
> 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.[/color]

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


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

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Jason]

Re: Trapping error messages

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns942092 3643DYazorman@1 27.0.0.1>...[color=blue]
> Shervin Shapourian (ShShapourian@h otmail.com) writes:[color=green]
> > 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.[/color]
>
> Alas, this is not possible. You need to have a co-operating client.[/color]

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]

Re: Trapping error messages

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

Shervin

"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns9420923 643DYazorman@12 7.0.0.1...[color=blue]
> Shervin Shapourian (ShShapourian@h otmail.com) writes:[color=green]
> > 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[/color][/color]
me.[color=blue]
>
> Alas, this is not possible. You need to have a co-operating client.
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[John Bell]

Re: Trapping error messages

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" <JayCallas@hotm ail.com> wrote in message
news:f01a7c89.0 310270900.2f811 2cd@posting.goo gle.com...[color=blue]
> Erland Sommarskog <sommar@algonet .se> wrote in message[/color]
news:<Xns942092 3643DYazorman@1 27.0.0.1>...[color=blue][color=green]
> > Shervin Shapourian (ShShapourian@h otmail.com) writes:[color=darkred]
> > > 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[/color][/color][/color]
me.[color=blue][color=green]
> >
> > Alas, this is not possible. You need to have a co-operating client.[/color]
>
> 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?[/color]