Linked Servers on MS SQL: Part 1

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$ 
	(address, au_fname)
VALUES
	(' 111 State St', 'Don')

This next return will verify our insert succeeded.

sp_addlinkedserver

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',
	'Excel',
	'Microsoft.Jet.OLEDB.4.0',
	'c:\temp\authors.xls',
	NULL,
	'Excel 8.0',
	NULL

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.