"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
