capturing error messages & storing them

[OhioSteve]

I run an application with an .asp front end (classic asp) and an sql server back end. I try to prevent the users from creating sql that will return an error. But in spite of my safeguards they still make mistakes. You have all seen error messages but here is an example~

Microsoft OLE DB Provider for SQL Server error '80040e2f'
Violation of PRIMARY KEY constraint 'PK_audit_company1'. Cannot insert duplicate key in object 'audit_company'.
/[folderName]/[folderName]/[folderName]/[webPage].asp, line 74

When they get one of these messages THEY USUALLY CLOSE THEIR BROWSER. This is driving me CRAZY because I never get to see the message. Is there a log somewhere that stores these messages? And if a log does not already exist, could I somehow make one?

 

[Madawc]

Could you persuade them to get a screen image using [Print Scrn] and save it on a Word document? I usually do this when I get a persistent error that I want someone to have a look at.

Screen images can also be pasted into e-mails, at least in Outlook 2002. But this makes the e-mails very large and likely to be stopped by anti-virus software, whereas the same thing on an attached word document is fine.

Madawc Williams (East Anglia, UK)

 

[SQLSister]

How are you sending the SQL query? Are you creating dynamic queries or using stored procedures?

If you are using stored procedures (which you should be for security and efficiency reasons), then you can capture the error code and insert it into a table which contains whatever information you need, such as the spname, the error code and the user.)

If you aren't using sps. you can do something simliar in ASP probably. I know our applications have an error log created to store all errors, but we are using .Net so I can't tell you how to approach this in ASP. Maybe the ASP forum can.

You might also consider if you need to do validation checks before sending an insert to the database. Then you can control how you tell the user what is wrong and how to fix it.


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[OhioSteve]

Here is an example of a simple stored procedure. How would I change it to capture error messages and write them to a table?

CREATE procedure dbo.x
@StartDate varchar(50),
@EndDate varchar(50)
as

--delete records outside of date parameters
DELETE FROM ourTable WHERE (inspstartdate > @endDate) or (inspstartdate < @startDate)

GO

 

[SQLSister]

After the Delete Put code simliar to this

If @@Error <>0
insert ErrorTable (ErrorNumber, SPName, Date, User)
Values (@@Error, 'spname', Getdate(), SYSTEM_USER)

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[OhioSteve]

That DID insert a record into the error table when the procedure failed. So that's great. However, it did NOT capture the text of the error message. Is "spname" really supposed to be a fixed value? That looks like a typo.

 

[OhioSteve]

Oh! I misunderstood you. You are saying that I need to insert the procedure's name as a string. That is a good suggestion but I want to capture the actual error message. For example, "...Violation of PRIMARY KEY constraint 'PK_keyname'. Cannot insert duplicate key in..."

 

[SQLSister]

NOw you have to join to the table which contains the error messages and error numbers. It's called sysmessages and is inthe master database. It is best to do the join when selecting the data rather than storing that extra information multiple times in the error table.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[OhioSteve]

I have been away from this project for awhile. SQLsister, I browsed sysmessages. It does seem to be some sort of list of errors. I am studying it now.

 

[OhioSteve]

Sysmessages gives you text that is concatenated with the names of the objects involved. I want to also capture those specific names. I want to capture the errormessage that the user sees when SQL Server chokes.