Creating a Heterogeneous Query with Microsoft SQL Server 7.0

Dave Mendlen
Visual Studio

November 1998

Summary: Examines the use of Microsoft® SQL Server™ 7.0's new linked servers to create a heterogeneous query that spans several data sources like Oracle, Access, and SQL Server 7.0. (8 printed pages) Covers:

• The purpose of a heterogeneous query
  
  
• How to create a linked server
  
  
• How to use these linked servers in Microsoft Visual Studio® version 6.0

With SQL Server 7.0's new linked servers you can create distributed queries that access data from Oracle, DB2, Sybase and any other data provider with an OLE DB driver. Now you have the ability to issue distributed queries, updates, commands, and transactions against all the data sources across the enterprise and return the results to a single result set. You can create a Web page that joins all the Customer information in DB2 with all the Order information in Oracle, joined to all the payment information from Informix.

Figure 1. Distributed query using linked servers

To run the query shown in Figure 1 without SQL Server 7.0's linked servers, you would have to write an Oracle PL/SQL command, and then a Microsoft Access JET/SQL command, and then a SQL Server Transact-SQL command. With linked servers, you can issue a single Transact-SQL command that will request data from all the data sources. And now you don't even have to write the SQL: You can do it all using the graphical query builder tools provided in Visual Studio.

Architecture

Figure 2. Linked server configuration

Figure 2 shows the basics of how a linked server configuration functions.

Linked servers are typically used to handle distributed queries. When a client application executes a distributed query through a linked server, SQL Server breaks down the command and sends rowset requests to OLE DB. OLE DB then queries the provider for the rowset information. The provider, in turn, knows the specifics of calling the data source, opening the file, and returning the requested information. The rowset data is returned through the provider and OLE DB to SQL Server, where it is reconstructed and returned to the client application as a result set and set of output parameters (if applicable).

Tutorial

This tutorial will show you how to connect to both Oracle and Access through SQL Server 7.0's new linked servers. This tutorial will work with either Oracle or Access or both connected to SQL 7.0. So if you don't have Oracle connected but want to see how to establish a linked server, jump down to the Access section and skip the Oracle setup.

Creating a Linked Server to Oracle

The first thing you need to do is to create a new linked server. The first linked server that we'll create will connect to Oracle. Because this technology utilizes SQL*NET, you can go against Oracle on any platform where Oracle runs.

Step 1: Open the SQL Server Query Analyzer.

Log in to SQL Server as an administrator. The user ID is probably "sa," with no password. Make sure you are in the master database context.

Figure 3. SQL Server Query Analyzer

Step 2: Create a new linked server.

This is a static, named connection to Oracle that you'll be using later. To create a new linked server, you can use the system stored procedure, sp_addlinkedserver.

You need to replace the following variable:

• SQLNET, which is the SQL*NET name for your connection to Oracle.

EXEC sp_addlinkedserver 'SQLNET', 'Oracle', 'MSDAORA', 'SQLNET'

Run the query.

Step 3: Create a new linked server login.

Now you need to create a SQL Server user login with permissions to log into your Oracle database. The sp_addlinkedsrvlogin system stored procedure creates the login and connects it to the new linked server.

You need to replace the following variables:

• SQLNET, which is the SQL*NET name for your connection to Oracle.
  
  
• system, which is the Oracle system administrator name.
  
  
• manager, which is the Oracle system administrator password.

EXEC sp_addlinkedsrvlogin 'SQLNET', 'FALSE', NULL, 'system', 'manager'"

Run the query.

Step 4: Create a view based on the new linked server.

One of the easiest ways to manipulate these connections to your remote data in Visual Studio is through Views. In the Query Analyzer, change your database context to Pubs. The view that we'll be creating is based on the Scott/Tiger demo database that comes with Oracle.

You need to replace the following variable:

• SQLNET, which is the SQL*NET name for your connection to Oracle.

Create View OracleSales as SELECT * FROM SQLNET..DEMO.SALES_ORDER

Run the query.

Creating a Linked Server to Microsoft Access

The second linked server that we'll create will connect to Access.

Step 1: Open the SQL Server Query Analyzer.

Log in to SQL Server as an administrator. The user ID is probably "sa," with no password. Make sure you are in the master database context.

Step 2: Create a new linked server.

This is a static, named connection to Access that you'll be using later. To create a new linked server, you can use the system stored procedure, sp_addlinkedserver. The database we're using works well in joining to both SQL Server 7.0 and Oracle.

You need to replace the following variable:

• LinkedServer, which is the path to the Microsoft Decision Support Services (DSS) sample database (The DSS tools ship with SQL Server 7.0). It's probably something like "c:\Program Files\Microsoft DSS\Samples\Foodmart.mdb."

EXEC sp_addlinkedserver 'Foodmart', '','Microsoft.Jet.OLEDB.4.0', 'LinkedServer'

Run the query.

Step 3: Create a new linked server login.

Now you need to create a SQL Server user login with permissions to log into your Access database. The sp_addlinkedsrvlogin system stored procedure creates the login and connects it to the new linked server. Unless you've set up security to this database, you can use the default Admin user ID for Access with no password.

You don't need to replace any variables.

EXEC sp_addlinkedsrvlogin 'Foodmart', false, NULL, 'Admin', NULL

Run the query.

Step 4: Create a view based on the new linked server.

One of the easiest ways to manipulate these connections to your remote data in Visual Studio is through Views. In the Query Analyzer, change your database context to Pubs. The view that we'll be creating is based on the FoodMart database that comes with DSS.

You don't need to replace any variables.

Create View AccessSales as SELECT customer_id AS Order_ID, product_id,
promotion_id, store_cost, store_id, store_sales, time_id, unit_sales 
FROM Foodmart.. .sales_fact_1998 WHERE (customer_id < 650) AND 
(customer_id > 500)

Creating a View in SQL Server 7.0

Finally, we'll create a SQL Server 7.0 view to be consistent with the rest of the views that we've created. We also need to insert some data that we can join on in the SQL Server database.

Step 1: Open the SQL Server Query Analyzer.

Log in to SQL Server as an administrator. The user ID is probably "sa," with no password. Make sure you are in the pubs database context.

Step 2: Create a new view.

We'll be using the SQL Server 7.0 Pubs database, which has a Sales table. This example view can be joined to the Oracle and Access views that we created earlier.

You don't need to replace any variables.

Create View SQL7Sales as SELECT stor_id, cast(ord_num AS int) AS 
order_ID, ord_date, qty, payterms, title_id FROM sales WHERE 
isnumeric(ord_num) = 1

Run the query.

Step 3: Insert shared data.

Now we need to add some rows of data to the SQL Server 7.0 Sales table so that all three data sources have rows that are in common.

You don't need to replace any variables.

INSERT INTO sales (stor_id, ord_num, ord_date, qty, payterms, title_id) 
VALUES ('6380', '511', '6/3/98', 82, 'Net 30', 'BU1032')

INSERT INTO sales (stor_id, ord_num, ord_date, qty, payterms, title_id) 
VALUES ('6380', '610', '6/1/98', 50, 'Net 30', 'BU1032')

INSERT INTO sales (stor_id, ord_num, ord_date, qty, payterms, title_id) 
VALUES ('6380', '520', '7/3/98', 12, 'Net 30', 'BU1032')

INSERT INTO sales (stor_id, ord_num, ord_date, qty, payterms, title_id) 
VALUES ('6380', '599', '1/25/98', 34, 'Net 30', 'BU1032')

Run the query.

Using the Linked Servers in Visual Studio 6.0

Let's create a new query that joins data from all three data sources.

Step 1: Create a new project.

1. From the Start menu, start Visual InterDev or Visual J++™.
   
   
2. To open a new database project, in the Start menu choose Database Project and click Open.
   
   
3. Click the Machine Data Source tab. Select an ODBC data source to SQL 7.0 as the data source.
   
   
4. Log on to the PUBS database.

   

Figure 4. SQL Server Login

Step 2: Create a new query.

1. Right-click the Views tree in the Data View window and select New View.
   
   
2. Click the + next to the Views tree to expand the tree to see all the views.
   
   
3. Drag the AccessSales, OracleSales, and SQL7Sales views to the gray area in the Query Builder window.

   

Figure 5. Building the query

Step 3: Create the relationships.

1. Click the Order_ID column in AccessSales (in the top pane), drag to the order_ID column in SQL7Sales, and release the mouse. Visual Studio 6.0 automatically creates the inner join for you, as seen in the SQL pane.
   
   
2. Click the order_ID in SQL7Sales, drag to the ORDER_ID column in OracleSales, and release the mouse.

Step 4: Choose the columns.

Now, simply choose the columns that you wish to use for the query by clicking next to the columns in all three tables.

Step 5: Run the query.

If this is the first time that you have run the query, it might take a few seconds to establish all the connections to the databases. Rerun the query to see the query run immediately.

Summary

Manually seaming together data from multiple data sources is incredibly difficult. With linked servers, you can issue a single Transact-SQL command that will request data from all the different data sources that you have. And now you don't even have to write the SQL: You can do it all using the graphical query builder tools provided in Visual Studio 6.0.