CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Linh
Linh

Reputation: 1022

Distributed query in SQL Server 2008

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.

Upvotes: 0

Views: 1357

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300719

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.

Might be relevant: FIX: Error message when you run a distributed query in SQL Server 2005 or SQL Server 2008: "OLE DB provider 'SQLNCLI' for linked server '' returned message 'No transaction is active'"

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.

Upvotes: 1

Related Questions