Need advice on Distributed Transaction

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Need advice on Distributed Transaction

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-11-07 : 08:26:02

Hi,

I've 2 server in the different location, which sql1 in 192.168.1.30 and sql2 in 192.168.1.31.

These is my scenario.
1. I need to update data into sql1
2. At the same time, i need to insert data into sql2

How to make it these 2 transaction (update in sql1 and insert sql2) guaranteed success. In other word, apply atomicity.

Need advice on this distributed transaction

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-07 : 09:55:18

If the servers are linked:

BEGIN TRAN

UPDATE	someTable
SET	someColumn = someValue;

INSERT	linkedServer.databaseName.schemaName.tableName (fieldList)
VALUES	(valueList);

IF	@@ERROR <> 0
BEGIN
	ROLLBACK
END
ELSE
BEGIN
	COMMIT
END