Linked Servers on MS SQL: Part 1 - Page 3

October 3, 2003

Four-Part Naming

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:

The location and catalog
parameters are not used in our example. A select will verify that our link was
successful.

Conclusion

Linked
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.