I'm learning about distributed query and using SQL Server 2008 to practice.
I got data from the linked server successfully via OPENROWSET statement.
Now I want to wrap some data modification statements in a transaction and execute at linked server. How do I do? Is this transaction same with the transaction at local server?
Moreover how do I implement security settings to prevent someone get password login to linked server? The password is a parameter of the sp_addlinkedsrvlogin procedure.
The query would need to participate in a distributed transaction (requires MSDTC to be installed and configured at both ends and appropriate ports on firewall opened...). Where possible, try to avoid holding open a transaction across distributed queries.
Distributed Transaction Issue for Linked Server in SQL Server 2008
Update (in response to question raised in comments):
From the wiki:
Database systems implement distributed transactions as transactions against multiple applications or hosts. A distributed transaction enforces the ACID properties over multiple systems or data stores, and might include systems such as databases, file systems, messaging systems, and other applications. In a distributed transaction a coordinating service ensures that all parts of the transaction are applied to all relevant systems. As with database and other transactions, if any part of the transaction fails, the entire transaction is rolled back across all affected systems.
