Using NT and SQL Server 7 to Access TurboIMAGE Databases

The NT environment and SQL Server 7 offer a powerful set of tools for developing new client/server and Web based applications. Most new applications, however, are not completely isolated from existing applications. They must use and generate data that is compatible with existing applications, that in some cases reside on a HP e3000.

SQL Server 7’s Linked Servers

SQL Server 7 introduced support for linked servers. Link servers represent connections to other databases. The databases can be non-SQL Server databases and can reside on any server reachable through the network. The connection is accomplished through an OLE DB provider. An OLE DB provider is a DLL that provides access to data from the supported database in a common way.

There are OLE DB providers for a number of databases, including Oracle and JET. The most interesting OLE DB provider, however, is the one for access to data through ODBC. Using this OLE DB provider, any database accessible through ODBC can be accessed as a SQL Server linked server. Once a database is configured as a linked server any SQL Server application can use the linked server’s tables just like any other table in the SQL Server database. The following diagram illustrates how a linked server is used to access a TurboIMAGE database on a HP e3000.

SQL Server linked servers are especially useful for new client/server applications that will use SQL Server as the main database, but need some access to legacy data on an HP e3000. The application could be make use of two connections, one to the SQL Server database and one through ODBC to the TurboIMAGE database. The main disadvantages of this scenario are:

1. The connection information for both databases would have to be configured on each client machine.
2. Each client machine would have to have an ODBC driver that can access HP e3000 database(s) installed on it. Additionally, an ODBC datasource for access to the HP e3000 database(s) would have to be duplicated on each client.
3. Each client machine would need network access to the HP e3000.

Using a linked server in the SQL Server database to access the HP e3000 database(s) resolves these problems as follows:

1. The connection information for the HP e3000 database(s) is part of the linked server configuration. It is kept and maintained on the SQL Server machine. The application only needs to know how to connect to the SQL Server machine.
2. The ODBC driver to access HP e3000 database(s) only needs to be installed on the SQL Server machine. The datasource for accessing the HP e3000 databases(s) would be kept and maintained on the SQL Server machine.
3. Each client only needs network access to the SQL Server machine. The SQL Server machine is the only one that needs network access to the HP e3000.

The main disadvantage of using a linked server is access to the HP e3000 database(s) is controlled by the OLE DB provider for ODBC. This will not always give the best performance.

Sample Application

Anwar, Inc. is an provider of telephony equipment. Each new customer gets assigned to a team at Anwar, Inc. which handles all of the customer’s needs. A typical team would consist of personnel from the sales, technical, and administrative departments at Anwar, Inc. At the present time, it is a time consuming and confusing task to introduce all the members of the team to the customer. Management at Anwar, Inc. would like IT to develop a Web site for the customer to use that would contain all of the team members, their pictures and their contact information.

At this point the personnel system and Anwar, Inc., which is on the HP e3000, has all of the employees in it with their contact information, but does not contain their pictures. Also the basic customer information is on the HP e3000, but team assignments for each customer are kept manually.

The proposal is to create a SQL Server database to store the team assignments and personnel pictures and use linked servers in the SQL Server database to access personnel contact information and customer information from the HP e3000. Since the application will be web-based, the NT machine that has the SQL server database, will also be a web server.

Two tables will go in the SQL Server database, CustomerAccess. They are:

The tables on the HP e3000 that will be accessed are:

In order to access the HP e3000 from the SQL Server machine, an ODBC datasource called HP e3000 will be set up. The configuration of the datasource will vary depending on the ODBC driver used. The following section shows the configuration in ODBC with ODBC Administrator.

Creating the ODBC Data Source

1. From the ODBC DataSource Administrator create a New User DSN called HP3000. If you need help creating the DSN refer to Chapter 3 (ODBC Manual) Configuring a Data Source with ODBC Administrator.
2. Once the DSN for accessing the HP e3000 database(s) is configured, the SQL Server Enterprise Manager can be used to configure a linked server.

Configuring a linked server

Using SQL Server Enterprise Manager:

1. Expand the Security tree and right mouse click on the Linked Servers label. Select New Linked Server and configure the linked server.
2. The linked server is given a name, OLE DB provider is selected, and the datasource for the OLE DB provider is named. In this case the OLE DB provider is Microsoft OLE DB Provider for ODBC Drivers and the datasource is HP3000.Note: If needed, there are additional options for security that may be configured.
3. At this time the linked server is ready to be accessed.

Creating sample application using the development tools

Any development tool that can access SQL Server can use linked servers. This would include Visual Basic, PowerBuilder, Delphi, FrontPage and many others. Lined servers also are very useful in developing queries that retrieve data from many different databases on many different machines. There are new applications available on the NT platform that will use SQL Server. Using linked servers is a powerful way to give these new applications access to legacy data.

In the following example we will use Visual InterDev to set up an Active Server Page to access data trough a SQL Server.

After a new Active Server Page and Data Connection have been created in a new project, the configuration for the Data Connection is as follows:

1. In the Connection1 Properties dialog box, select Build under the General tab to build your connection string.
2. Under the Provider tab select Microsoft OLE DB Provider for SQL Server and select Next
3. Select the Connection tab and enter your valid user login information and select Test Connection. If connection proves successful, select OK.
4. Selecting OK from the Data Link Properties dialog box will bring you back to the Connection1 Properties dialog box, select OK.
5. After the data connection is created and tested a RecordSet control is dropped on the Active Server Page and configured. This configuration will control how the data is retrieved from the databases. In this case four data tables will need to be joined by an SQL SELECT statement, to execute a distributed query against the SQL Server

The data access of the Active Server Page is complete. Additional work would have to be done to format the page, and add controls to display the data. Most likely, an additional start-up page, querying the user for their customer number and password would be added, this would be linked to the Active Server Page when the customer was validated