469,647 Members | 1,725 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

Logging Errors

SQL Server 2000 BE, Access 2002 FE.

I want to write a stored procedure, that will among other things log
errors to a table, I want to be able to report a summary of work done
and errors to the user and let them decide whether to commit or
rollback. I so far foresee a couple of problems.

1. Inside a transaction, I write to the error log table, will I be able
to read it back again before commit/rollback?

2. If I rollback, my error log gets rolled back too.

Is it possible to log the errors outside of the current transaction?
Jul 20 '05 #1
2 2270

"Trev@Work" <no.email@please> wrote in message
news:41***********************@news.easynet.co.uk. ..
SQL Server 2000 BE, Access 2002 FE.

I want to write a stored procedure, that will among other things log
errors to a table, I want to be able to report a summary of work done and
errors to the user and let them decide whether to commit or rollback. I so
far foresee a couple of problems.

1. Inside a transaction, I write to the error log table, will I be able to
read it back again before commit/rollback?

2. If I rollback, my error log gets rolled back too.

Is it possible to log the errors outside of the current transaction?


Yes, yes and no (assuming nothing like an extended proc, or writing to a
file outside the database). But in any case, keeping a transaction open
pending user input is not a good idea - sooner or later someone will go for
a coffee or lunch, leaving a transaction open and everyone else blocked. And
on top of that, many MSSQL errors can't be handled on the server side anyway
(if that's the kind of error you meant):

http://www.sommarskog.se/error-handling-I.html

I don't know exactly what you're doing, but you should probably retrieve the
data to the client, let the user review and update it there, then write it
back. You can use use a rowversion/timestamp column to detect if the data in
the base tables has changed between retrieving it and updating it.
Alternatively, you may be able to use application locks (see sp_getapplock)
in Books Online to define your own locking approach for your application.

If this isn't helpful, you might want to post some more details of what you
need to do, and what sort of errors you want to trap.

Simon
Jul 20 '05 #2
Simon Hayes wrote:
"Trev@Work" <no.email@please> wrote in message
news:41***********************@news.easynet.co.uk. ..
SQL Server 2000 BE, Access 2002 FE.

I want to write a stored procedure, that will among other things log
errors to a table, I want to be able to report a summary of work done and
errors to the user and let them decide whether to commit or rollback. I so
far foresee a couple of problems.

1. Inside a transaction, I write to the error log table, will I be able to
read it back again before commit/rollback?

2. If I rollback, my error log gets rolled back too.

Is it possible to log the errors outside of the current transaction?

Yes, yes and no (assuming nothing like an extended proc, or writing to a
file outside the database). But in any case, keeping a transaction open
pending user input is not a good idea - sooner or later someone will go for
a coffee or lunch, leaving a transaction open and everyone else blocked. And
on top of that, many MSSQL errors can't be handled on the server side anyway
(if that's the kind of error you meant):

http://www.sommarskog.se/error-handling-I.html

I don't know exactly what you're doing, but you should probably retrieve the
data to the client, let the user review and update it there, then write it
back. You can use use a rowversion/timestamp column to detect if the data in
the base tables has changed between retrieving it and updating it.
Alternatively, you may be able to use application locks (see sp_getapplock)
in Books Online to define your own locking approach for your application.

If this isn't helpful, you might want to post some more details of what you
need to do, and what sort of errors you want to trap.

Simon


Hi Simon, thanks for the reply. I'm doing an import, in this case an MTO
(Material Take Off from CAD into a procurement system). The errors I
will trap here are data errors, e.g. if a delta quantity would cause a
cumulative quantity to go below zero or the revision of a drawing being
imported is already in the drawing archive (i.e. they are importing an
older one than they should be).

I see your point about people going for coffee, I do it myself <g> I
have re-designed to take into account any locking problems associated
with transactions so that now I pre-process everything and mark the
imported records with error codes, I can then generate an error listing
before I even touch the live data.

Any other errors that occur (thanks for the link) I will be rolling back
everything anyway, the user will have to like it or lump it :-)
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by A.M | last post: by
4 posts views Thread by Denise | last post: by
7 posts views Thread by Leo Breebaart | last post: by
7 posts views Thread by flupke | last post: by
reply views Thread by rajesh.hanchate | last post: by
3 posts views Thread by nicholas.petrella | last post: by
4 posts views Thread by Alexandru Mosoi | last post: by
6 posts views Thread by Thomas Heller | last post: by
1 post views Thread by arunairs | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.