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 |
|
djokerss
Starting Member
28 Posts |
 Posted - 2004-10-10 : 21:36:58
|
| i've made a store procedure to saving data like this ..begin transaction xpostingdeclare cursor_name cursor scroll for ... etcopen cursor_namefetch next from cursor name into @xdeclarationwhile @@fetch_status=0begin ----------------> say it proses 1------- some proses to update data------- whith @xdeclaration as referensi------- and there is some record that fail to update data------- ( for example there's 5 record but 2 was failed to update )endclose cursor_namedeallocate cursor_nameupdate table y set posting=1 <------ update another table ( proses 2 )if @@error=0commit tran xposting else rollback tran xposting.........my problem is when there's failed proses in proses 1 why the proses 2 still prosessing...not rollback.is there some mistake with my begin transaction .. end transaction ? |
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-10-11 : 12:10:49
|
is this proses2 update table y set posting=1 <------ update another table ( proses 2 ) firstI think SQL haven't found any errors or else the SP would have failed unless you have a SET XACT_ABORT or something else to handle the errorssecondthe if @@error=0 is after proses2 maybe you should add that line before proses2 too*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
 |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2004-10-11 : 12:35:17
|
| You need to test for errors after each statement, as @@error will only return the error number for the last statement executed. As process 2 executes successfully, at this point, @@error = 0.Mark |
|
|
|
|
|
|
about begin transaction...