Linking SQL Server to Heterogeneous Systems

Friday Feb 28th 2003 by DatabaseJournal.com Staff
Share:

A brief look into linking SQL Server to such systems as Oracle, DB2, Sybase, etc., through Open Database connectivity.

MAK (Muthusamy Anantha Kumar)

The ability to link SQL Server to heterogeneous systems such as Oracle, DB2, Sybase, etc., is imminent. This article provides the basic and most important information required to make a linked server connection to heterogeneous systems from SQL Server. We are going to deal with three different RBBMS.

  • DB2
  • Sybase
  • Oracle

Prerequisite:

MSDTC service should be running in the SQL Server Box.

Linked server to DB2 database in mainframe S/390:

Install and Configure Mainframe Client

The known ODBC client for connecting to Mainframe S/390 DB2 is Neon 32 bit drivers. The first step in this process is to install Neon Client on the SQL Server box. Then create the ODBC system DSN to mainframe. Provide the Database Source name, Host name, userID, password and port number in the DSN Setup. In addition, provide the Subsystem information about your mainframe in the DSN setup. Change the connection mode to "Transactional," the X/OPEN XA support to TWO-Phase commit, and X/OPEN XA Transaction manager to MSDTC. Follow the Snapshots below.

Create Linked Server for DB2

Open enterprise manager, right click on Linked server under the Security Folder, and create a new linked server. Select Microsoft OLE DB Provider for the ODBC Drivers. Give the same system ODBC name that you created for DB2 in the textbox provided for Data Source. Finally, provide a Userid and password in the provider string and security tab.

Test the connection by running the following sql command in query analyzer.

Select * from openquery(DB2C,"Select column1 from table1")
MAK (Muthusamy Anantha Kumar)

Linked server to Sybase 12 ASE database :

Install and Configure Sybase Client

The first step in this process is to install Sybase 12 ASE drivers on the SQL Server box. Then create an ODBC system DSN to Sybase. Get the IP address, database name, userID, password and port number used in Sybase server, from the Sybase database admin. The Sybase client reads information about Sybase from a configuration file under \Sybase\ini\sql.ini file. (Example appears below). Change or create the following information in the sql.ini file.

If windows 2000 Professional:

[MySystem]
master=TCP,172.277.78.118,2055
query=TCP,172.277.78.118,2055

If windows 2000 server use this (remove comma between port and ip address):

[MySystem]
master=TCP,172.277.78.118 2055
query=TCP,172.277.78.118 2055

Create the ODBC system DSN for Sybase server on the SQL Server Box.

Create Linked Server for Sybase

Open the Enterprise manager, and right click on Linked server under the Security Folder. Create a new linked server. Select the OLE DB Provider for ODBC Drivers. Give the same system ODBC name that you created for Sybase in the textbox provided for Data Source. Provide a userid and password in the provider string and security tab.

Test the connection by running the following SQL command in query analyzer.

Select * from openquery(SybaseServer, "Select column1 from table1")
MAK (Muthusamy Anantha Kumar)

Linked server to Oracle database :

Install and Configure Oracle Client

Get the IP address, database name, userID, password and port number used in Oracle server, from the Oracle database administrator. Install the Oracle client on the SQL Server box. Oracle client reads information about Oracle from the TNSNAMES.ORA file. Edit the Edit the TNSNAME.ORA file and update it with appropriate information, (ex. Below).

MyOracle =
  (description =
          (address_list =
                (address =
                 (community = Oraworld.world)
                 (protocol = tcp)
                 (host = 177.123.12.201)
                 (port = 1527)
                 )
            )
   (connect_data = (sid = oradatabase )

    ))

Create the ODBC System DSN for Oracle with the appropriate information on the SQL Server Box.

Create Linked Server for Oracle

Open the Enterprise manager, and right click on Linked Server under the Security Folder. Create a new linked server. Select OLE DB Provider for the Oracle Drivers. Give the same system ODBC DSN name that you created for Oracle in the textbox provided for DataSource. Provide userid and password in the provider string and security tab.

Test the connection by running the following SQL command in query analyzer.

Select * from openquery(MyOracle, "Select column1 from table1")

These are the major heterogeneous databases that we can access from SQL server using 3rd party supplied drivers and ODBC drivers. This will give you a general idea of how SQL server interacts with heterogeneous databases through Open Database connectivity.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved