Microsoft® SQL Server® 2012 Bible

379

Chapter 15: Executing Distributed Queries

15

timeout, username and password, and catalog. The init string must defi ne the entire

external data-source connection and security context within the function. You do not need

to use quotes, but avoid the common mistake of mixing the commas and semicolons in the

OpenDatasource() function.

The following example is a relatively simple example that illustrates how to use the

OpenDatasource() to connect to another SQL Server instance:

SELECT EmployeeID,NationalIDNumber, LoginID

FROM OPENDATASOURCE(

'SQLOLEDB',

'Data Source=AvalonDev;User ID=Scott;Password=P@$$W0rd'

).AdventureWorks.HumanResources.Employee

What about non-SQL data sources? No problem there. The following example illustrates how

to use the OpenDatasource() against an Access database:

SELECT EmployeeID,NationalIDNumber, ContactID

FROM OPENDATASOURCE(

'Microsoft.Jet.OLEBDB.4.0',

'Data Source= D:\Projects\Wiley\SQL Server 2011 Bible\Nwind.mdb'

)...Customers

You can also use the OpenDatasource() to perform DML operations as well, as shown in

the following example:

UPDATE OpenDatasource(

'Microsoft.Jet.OLEBDB.4.0',

'Data Source= D:\Projects\Wiley\SQL Server 2011 Bible\Nwind.mdb'

)...Employee

SET Title = 'Geek'

WHERE EmployeeID = 5

The same applies to an Excel worksheet:

UPDATE OpenDatasource(

'Microsoft.Jet.OLEBDB.4.0',

'Data Source= D:\Projects\Wiley\SQL Server 2011 Bible\

AdventureWorksContacts.xls;

Extended Properties=Excel 5.0'

)...People

SET LastName = 'Abelito'

WHERE FirstName = 'Catherine'

To verify your changes, execute the following query:

SELECT *

FROM OpenDatasource(

'Microsoft.Jet.OLEBDB.4.0',

'Data Source= D:\Projects\Wiley\SQL Server 2011 Bible\

AdventureWorksContacts.xls;

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

http://www.it-ebooks.info