Microsoft® SQL Server® 2012 Bible

376

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

With the link to the spreadsheet defi ned, you can now query the specifi c range just as you

would query a normal table, as follows:

SELECT * FROM AdventureWorksContacts...People

Partial results of the query are shown here:

FirstName LastName EmailAddress

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

Gustavo Achong [email protected]

Catherine Abel [email protected]

Kim Abercrombie [email protected]

Humberto Acevedo [email protected]

Pilar Ackerman [email protected]

Excel spreadsheets are not multiuser spreadsheets. SQL Server can’t perform a distributed

query that accesses an Excel spreadsheet while that spreadsheet is open in Excel.

Now take a look at linking to Microsoft Access.

Linking to Access

SQL Server links easily to Microsoft Access, using the OLE DB Jet provider to connect to Jet

data sources and execute queries. Because Access is a database, there are no special steps

for preparing an Access data for linking as there is for Excel (creating named ranges). Every

Access table appears as a table under the Linked Servers node in Management Studio.

For this example, use the sample NWind Access database from the Microsoft MSDN site,

which the download materials for this book also include.

The following example links the NWind.mdb Access database to SQL Server so that you can

retrieve data:

exec sp_addlinkedserver

@server='Nwind',

@srvproduct = 'Access 2003',

@provider='Microsoft.Jet.OLEBDB.4.0',

@datasrc='D:\Projects\Wiley\SQL Server 2011 Bible\Nwind.mdb'

With the Access database linked, you can execute queries, such as the following:

SELECT * FROM Nwind...Customers

Partial results of the query are shown here:

Customer ID Company Name Contact Name

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

ALFKI Alfreds Futterkiste Maria Anders

ANATR Ana Trujillo Empa Ana Trujillo

ANTON Antonio Moeno Taquiria Antonio Moreno

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

http://www.it-ebooks.info