Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 4, 2003

Linked Servers on MS SQL: Part 1 - Page 3

By Don Schlichting

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:

	(address, au_fname)
	(' 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',
	'Excel 8.0',

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


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.

» See All Articles by Columnist Don Schlichting

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM