By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,569 Members | 1,422 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,569 IT Pros & Developers. It's quick & easy.

Logging Errors

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a

"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

P: n/a
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.