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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 3, 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:

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.

» See All Articles by Columnist Don Schlichting



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date