Microsoft® SQL Server® 2012 Bible

381

Chapter 15: Executing Distributed Queries

15

The following example illustrates the same but against the Excel spreadsheet used in earlier

examples:

SELECT *

FROM OPENQUERY(AdventureWorksContacts, 'SELECT * FROM People WHERE

LastName = "Brown"');

Here again, the remote scan returned the necessary rows (in this case 3).

OK, a quick UPDATE example — for DML executions, the exact rows requiring the update

return to SQL Server where the update takes place. The results are written back to the

external data source.

UPDATE OPENQUERY(

AdventureWorksContacts,

'SELECT * FROM People WHERE LastName = "Brown"')

SET LastName = 'Browns';

The great thing about the OpenQuery() function is that it handles changes in server con-

fi guration without requiring any changes to the code.

OpenRowSet()

The OpenRowSet() function is the pass-through counterpart to the OpenDataSet()

function. Both require the remote data source to be fully specifi ed in the distributed query,

but the OpenRowSet() function adds a parameter to specify the pass-through query.

SELECT [First Name], [Last Name]

FROM OPENROWSET(

'Microsoft.Jet.OLEDB.4.0',

'D:\Projects\Wiley\SQL Server 2011 Bible\Nwind.mdb',

'SELECT * FROM Employees');

Updates operate the same way:

UPDATE OPENROWSET(

'Microsoft.Jet.OLEDB.4.0',

'D:\Projects\Wiley\SQL Server 2011 Bible\Nwind.mdb',

'SELECT * FROM Employees WHERE [Employee ID] = 5')

SET Title = 'Geek';

Using Distributed Transactions

Transactions are key to data integrity — that goes without saying. If the logical unit of work

includes modifying data outside the local SQL Server, standard transactions cannot handle

the atomicity of the transaction. If a distributed transaction fails during the processing of the

operation, how is it handled? Who is responsible for handling that error and execution?

A mechanism must be in place to roll back the partial work; otherwise, a partial transac-

tion would be recorded and the database left in an inconsistent state. This cannot happen.

c15.indd 381c15.indd 381 7/30/2012 4:50:40 PM7/30/2012 4:50:40 PM

http://www.it-ebooks.info