This type of query on a
linked server access is called a "Direct Reference." We are directly
referencing the object (Sheet1$ in this case) in our query, as opposed to using
a function to help setup the connection, or executing a stored procedure
located on the linked server. Direct Referencing only works when the OLE DB
provider supports Four-Part Naming. The naming parts are Link Name, Database
Name, Owner and Object. We will be exploring more on four-part naming when we
start connecting to traditional databases as linked servers. For additional
information on Four-Part Naming, see BOL Four-Part Naming or IDBSchemaRowset.
IDBSchemaRowset is the meta data handler for linked servers.
Back to Excel
The first row of the Excel
sheet has automatically been used as Column headings for our table, allowing us
to query the Excel sheet in standard TSQL statements such as:
SELECT address FROM EXCEL_AUTHORS...Sheet1$.
The usual DML (Data
Manipulation Language) commands are also
supported. Such as:
INSERT INTO EXCEL_AUTHORS...Sheet1$
(' 111 State St', 'Don')
This next return will verify
our insert succeeded.
Using the system stored
procedure sp_addlinkedserver instead of Enterprise Manager can also create the
linked server. Following is the sp syntax:
sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]
To repeat the Excel example
used previously, use the following parameters:
EXEC sp_addlinkedserver 'EXCEL2',
The location and catalog
parameters are not used in our example. A select will verify that our link was
Servers are a great way to include outside data sources into your TSQL. There
are providers for most desktop applications. In future articles, we will begin
using security, passwords, additional Query Analyzer commands and connecting to
SQL, Access and Oracle databases.
See All Articles by Columnist Don Schlichting