|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 03/17/2006 : 08:28:24
|
| Kammer writes "I am using two DBs (myDB1 and myDB2) in two SQL 2000 Servers (mySQLServer1 and mySQLServer2) respectively, but in on of my application process I want to insert into a table called myTable1 in myDB1 and I also want to update the corresponding data in a table called myTable2 in myDB2. But the problem is I have to complete the whole process as a transaction. If anything go wrong during the process, all the insert and update should be rollbacked." |
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 03/17/2006 : 10:02:17
|
Call an sp
create proc xxxx
@val1 varchar(12) ,
@val2 varchar(12)
as
begin tran
insert tbl1 (val1) select @val1
if @@error <> 0
begin
raiserror('failed', 16, -1)
rollback tran
return
end
update db2..tbl2 set val1 = @val1 where val2 = @val2
if @@error <> 0
begin
raiserror('failed', 16, -1)
rollback tran
return
end
commit tran
==========================================
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. |
 |
|
Distributed Transaction
Forum Locked
