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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Distributed Transaction

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-17 : 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

12543 Posts

Posted - 2006-03-17 : 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.
Go to Top of Page
   

- Advertisement -