Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SS7,SS2K
Expertise: Intermediate
May 5, 2001

How to perform a distributed query using a linked server on SQL Server

One of the neatest features in SQL Server 7 and later versions is the capability to query external data sources through their OLE DB providers, in other words behaving exactly as an ordinary OLE DB data consumer. Queries performed in this way are known as distributed queries and can be implemented through different techniques (see SQL Server's Books on Line for more information):
  • the OpenQuery T-SQL function
  • the OpenRowset T-SQL function
  • the so-called four-part name syntax of T-SQL
The following VB code shows how it is possible to create a linked server that points to a Microsoft Access database. The linked server is created through the sp_addlinkedserver stored procedure (learn more about this procedure in Books on Line). The example also shows how you can use the sp_addlinkedserverlogin stored procedure, which is necessary to access password-protected databases:
 
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=master;User " _
    & "Id=sa;Password=;"
cn.Execute "EXEC sp_addlinkedserver 'MyLinkedServerName', 'Jet 4.0', " _
    & "'Microsoft.Jet.OLEDB.4.0', 'c:\program files\microsoft " _
    & "office\office\samples\northwind.mdb'"
cn.Execute "EXEC sp_addlinkedsrvlogin ' MyLinkedServerName ', FALSE, NULL, " _
    & "'admin', ''"
cn.Close
Set cn = Nothing
Once you have defined the linked server, you can perform queries on it, and these queries can even include JOIN operations with tables in other SQL Server databases. The code below shows how you can read data from the Customers table in Northwind.mdb, using the three techniques mentioned previously:

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim SQL As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

' the OPENQUERY function
SQL = " Select a.* from OPENQUERY(MyLinkedServerName, 'Select * from " _
    & "Customers') a"

' the OPENROWSET function
SQL = "SELECT * From OPENROWSET('Microsoft.Jet.OLEDB.4.0','c:\program " _
    & "files\microsoft office\office\samples\northwind.mdb'; 'Admin';'', " _
    & "Customers)"

' the four-part syntax
SQL = "Select * from MyLinkedServerName...Customers"
cn.CursorLocation = adUseClient
cn.Open "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=master;User " _
    & "Id=sa;Password=;"
rs.Open SQL, cn, adOpenStatic, adLockReadOnly
' ...
rs.Close
set rs = Nothing
cn.Close
Set cn = Nothing
As you see, it is a powerful technique, that empowers SQL Server developers with the capability to perform heterogeneous queries, that Access programmers have since a long time.

########################################################

This tip has been originally published on Microsoft Italia's web site.
It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia.
You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli

########################################################

Giuseppe Dimauro
 
Comment and Contribute

 

 

 

 

 


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

 

 

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