Microsoft® SQL Server® 2012 Bible

374

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

Linking to Non-SQL Server Data Sources

You have spent quite a bit of time considering linking to external SQL Server data sources

but rarely is all the information you need stored in SQL Server. Frequently, you need to query

data from data sources, such as Excel, Access, or other database systems such as Oracle.

Getting access to that data, however, depends on the availability and the features of the

ODBC drivers and the OLE DB providers. SQL Server uses OLE DB for the external data, and

SQL Server includes several OLE DB providers.

Setting up a linked server to a non-SQL Server data source is similar to a SQL Server link.

A data source (location) and possibly a provider string to supply additional information is

essentially all you need. Table 15-1 lists some of the more common data sources and

their settings.

TABLE 15 -1 Common External Data Sources

Data Source Provider Name

Use in

Four-Part

Names

Use in Pass-

Through Queries

and Commands

Use in INSERT,

UPDATE, or

DELETE

Use in

Distributed

Transactions

SQL Server SQLNCLI Yes Yes Yes Yes

ODBC MSDA SQL Yes Yes Yes Yes

Access Microsoft.Jet.

OLEDB.4.0

Yes Yes Yes No

Excel Microsoft.Jet.

OLEDB.4.0

Yes Yes Yes No

Oracle OraOLEDB.Oracle Yes Yes Yes Yes

File

System

MSIDXS No Yes No No

DB2 DB2OLEDB Yes Yes Yes Yes

Exchange No Yes No No

In this section, you link to an Excel spreadsheet to get data and then link to an Access

database.

Linking to Excel

With Microsoft Excel, each spreadsheet page and named range appears as a table when

accessed from an external data provider. Within Excel, you can create a named range in

several ways. The easiest way to create a named range is to highlight the range of cells you

want to defi ne as a “table,” and then when highlighted, enter the name of the range in the

space above the A1 cell, as shown in Figure 15-2.

c15.indd 374c15.indd 374 7/30/2012 4:50:39 PM7/30/2012 4:50:39 PM

http://www.it-ebooks.info