Microsoft® SQL Server® 2012 Bible

378

Part III: Advanced T-SQL Data Types and Querying Techniques

and processed. These types of queries are often called T-SQL distributed queries simply

because the queries use T-SQL syntax.

Local distributed queries use the four-part name discussed earlier if the data is on another

SQL Server. For example, you can use the four-part name in any SELECT or data modifi ca-

tion query:

SELECT BusinessEntityID, NationalIDNumber, LoginID

FROM AvalongDev.AdventureWorks2012.HumanResources.Employee

Following are the partial results of the query:

EmployeeID NationalIDNumber LoginID

---------- ---------------- ---------

1 14417807 adventure-works\ken0

2 253022876 adventure-works\terri0

3 509647174 adventure-works\roberto0

4 112457891 adventure-works\rob0

5 480168528 adventure-works\gail0

When performing DML operations as a distributed query, you must substitute either

the four-part name or a distributed query function for the table name. The following

example uses the four-part name as the source for an INSERT statement against the same

AdventureWorks database.

INSERT Contact(Name)

SELECT [First Name]

FROM AdventureWorksContacts...People

You can also do the same with updates:

UPDATE AvalonDev.AdventureWorks.Person.Contact

SET Title = 'Geek'

WHERE ContactID = 5

OpenDataSource()

Using the OpenDatasource() function is equivalent to using a four-part name to access

a linked server, except that the OpenDatasource() function defi nes the link within the

function instead of referencing a predefi ned linked server. Although defi ning the link in

code bypasses the linked server requirement, if the link location changes, the change can

affect every query that uses the OpenDatasource() function. The OpenDatasource()

also does not accept variables as parameters.

The basic OpenDatasource() syntax is quite simple:

OPENDATASOURCE(provider_name,init_string)

The OpenDatasource() function is substituted for a server in the four-part name and

may be used with any DML statement. However, there is more to it than this. The init

string is a delimited string containing several parameters. The parameters depend on the

type of data source but include the data source, location, extended properties, execution

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

http://www.it-ebooks.info