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

"Shervin Shapourian" <ShShapourian@hotmail.com> wrote in message
news:vpj76ijcj94g57@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

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.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
>
>

Shervin Shapourian (ShShapourian@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, sommar@algonet.se

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

Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns9420923643DYazorman@127.0.0.1>...
> Shervin Shapourian (ShShapourian@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?

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

Shervin

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns9420923643DYazorman@127.0.0.1...
> Shervin Shapourian (ShShapourian@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, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

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@hotmail.com> wrote in message
news:f01a7c89.0310270900.2f8112cd@posting.google.c om...
> Erland Sommarskog <sommar@algonet.se> wrote in message
news:<Xns9420923643DYazorman@127.0.0.1>...
> > Shervin Shapourian (ShShapourian@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?