Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2004-06-20 : 14:53:33
|
I have some code likeBEGIN TRANSACTION... do some SQL ...INSERT INTO MyLogTable (SomeColumns) VALUES (SomeValues)... do some SQL ...INSERT INTO MyLogTable (SomeColumns) VALUES (MoreValues)... do some SQL ...IF @@ERROR <> 0 ROLLBACK Is there some way I can preserve the logged data and not have it rollback?I need the Log Data to work out why the SProc is rolling back ... :(I'm thinking INSERT into another database, or use some ghastly Command Shell to OSQL it in, but all ideas welcome.Kristen |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-06-20 : 15:00:34
|
| At the error point, you know something has gone wrong -- so write out to your own error table, egBEGIN TRANSACTION... do some SQL ...--- save those important values into some local vars... do some SQL ...--- save those important values into some local vars... do some SQL ...IF @@ERROR <> 0 ROLLBACKinsert into mylogtable (more cols) values (saved values)you might also want to store the error code tooGraham |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-20 : 17:00:20
|
| Thanks for that, but unfortunately my code is a bit more complicated than I indicated ...... the SProc in question is itself being called by an SProc, which detects that the "child" SProc had a problem and rolls back its transaction too ... so anything in the child SProc is lost :(Kristen |
|
|
|
gpl
Posting Yak Master
195 Posts |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-20 : 18:29:33
|
| Have the OUTPUT of the child procedure be the @error message you're throwing around. Instead of testing the return, test the @error OUTPUT. If it's null, the child was successful, otherwise the parent procedure can the rollback and save the error message. I put the name of the procedure in my error messages so I know if it came from the parent or child for example.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-21 : 00:39:52
|
| Thanks Graham, I did think of file output but am a bit relucantant as its more of a pain to get at the data if its in files. But I can BCP it back in I guess ... Also need something that appends but I expect BCP can do that.Derrick: I was hoping to output the data to log, rather than return an error message of some sort, 'coz there's lots of lovely logging already in the SProc, and the log-data would lose a lot of its structure etc. if I return it as, say, a varchar - not even sure it would all fit, but.Now if I knew WHY it was going wrong I could return a nice shiny error message!Kristen |
|
|
|
|
|
|
|
|
Bypass ROLLBACK to force save?