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

 Dim cn As ADODB.ConnectionSet cn = New ADODB.Connectioncn.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 filesmicrosoft " _    & "officeofficesamples
orthwind.mdb'"cn.Execute "EXEC sp_addlinkedsrvlogin ' MyLinkedServerName ', FALSE, NULL, " _    & "'admin', ''"cn.CloseSet 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.RecordsetDim cn As ADODB.ConnectionDim SQL As StringSet cn = New ADODB.ConnectionSet rs = New ADODB.Recordset' the OPENQUERY functionSQL = " Select a.* from OPENQUERY(MyLinkedServerName, 'Select * from " _    & "Customers') a"' the OPENROWSET functionSQL = "SELECT * From OPENROWSET('Microsoft.Jet.OLEDB.4.0','c:program " _    & "filesmicrosoft officeofficesamples
orthwind.mdb'; 'Admin';'', " _    & "Customers)"' the four-part syntaxSQL = "Select * from MyLinkedServerName...Customers"cn.CursorLocation = adUseClientcn.Open "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=master;User " _    & "Id=sa;Password=;"rs.Open SQL, cn, adOpenStatic, adLockReadOnly' ...rs.Closeset rs = Nothingcn.CloseSet 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

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