473,375 Members | 1,565 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,375 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 5536

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Nithi Gurusamy | last post by:
Dear Group: I have a COM object developed in VB. It makes ADODB calls. When it fails it Raise Error. I am using the COM object in my ASP using Server.CreateObject. Whenever a function call fails...
8
by: Pete | last post by:
I'm trying to improve my code so that when I open a recordset object, I can absolutely guarantee it is closed and is set = Nothing. I have read some old threads and they all say to use the...
6
by: Tony Williams | last post by:
When a user deletes a record from a table which has a link to another table two messages appear.One that asks the user to confirm they want to delete the record and another that involves the...
3
by: Nathan Bloomfield | last post by:
Hi there, I am having difficulty with a piece of code which would work wonders for my application if only the error trapping worked properly. Basically, it works as follows: - adds records...
21
by: Neil | last post by:
Is there a way to trap an error generated in another app that is controlled via automation? I have an Access 2000 app that opens Word 2000 and proceeds to open a series of documents and, in each...
13
by: Thelma Lubkin | last post by:
I use code extensively; I probably overuse it. But I've been using error trapping very sparingly, and now I've been trapped by that. A form that works for me on the system I'm using, apparently...
2
by: Captain Nemo | last post by:
I'm still using Office 2000 myself, but some of my clients have Office 2003. I've recently added a piece of code to create an instance of Word, open a document, fill in the blanks and become...
9
by: 47computers | last post by:
Pretty new to PHP, I recently started learning about error trapping. As of right now, I include the following into a page in my website: -------BEGIN PASTE-------- error_reporting(E_ERROR |...
4
by: franc sutherland | last post by:
Hello, I am using Access 2003. I am having trouble trapping the "can't append all the records in the append query" error message when appending data to a query from a table which is linked to...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.