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 |
|
Sheetal
Starting Member
10 Posts |
 Posted - 2007-10-23 : 11:06:27
|
| Hi Sir,I have a task in hand To extract data from one DB to another (SAME SERVER). For this I have written one SP which use one batch to do this task.INSERT INTO DB1..TABLE1(COL1, COL2)SELECT COL3, COL4FROM DB2..TABLE2I have also used TRY and CATCH block in the SP. My PM says you have to write Transaction inside SP because if this fails then you must rollback the same. My question is: Shud I write one Tran under one SP? or SP will take care of rollback?Regards,Sheetal |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-23 : 11:17:39
|
| Nope - the statement will be atomic so there's no need for explicit transaction controlIt will either succeed or fail (in which case it will rollback).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Sheetal
Starting Member
10 Posts |
Posted - 2007-10-24 : 11:31:09
|
| Thanks Sir,Suppose I have 20 batches in my SP and SP execution fails at batch no 12. Do all the batches from 1 to 12 will rollback?Sheetal |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-24 : 11:48:54
|
| no they won't._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-25 : 11:21:30
|
| begin trybegin tranupdate tbl .....update tbl .....commit tranend trybegin catchrollback tranraiserror('something wrong', 16, -1)end catch==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Transaction under one SP