How to connect to SQL Server database through ODBC using Toad for Oracle and Database Link. (4310436)

How to connect to SQL Server database through ODBC using Toad for Oracle and Database Link.

Creating a DB Link from an Oracle Database to a SQL Server database is not a Toad for Oracle issue. Please have your DBA or network admin configure the settings to create the DB Link. The following is a guideline in doing so. Once it is established, you will be able to query the SQL Server database through Toad for Oracle.

A. Create a DSN (Data Source Name) for the SQL Server connection.

1. Click Start | Control Panel | Administrative Tools | Data Sources (ODBC )

2. Click System DNS tab, then click Add. Select your SQL Server driver. Click Finish.

3. Give the connection a name (MYSQLSERVERDSN), description, and server information. Click Next.

4. Configure your database settings and click next until wizard is complete.


B. Create a Heterogeneous Services Initialization File

1. Oracle provides a sample initialization file in the following directory:

$ORACLE_HOME/hs/admin/inithsodbc.ora

2. Create your own initialization file, pointing to the SQL Server DSN.

$ORACLE_HOME/hs/admin/initMYSQLSERVERDSN.ora altered file:
# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MYSQLSERVERDSN
HS_FDS_TRACE_LEVEL = OFF


C. Alter your listener.ora file.

$ORACLE_HOME/network/admin/listener.ora altered file:

LISTENERMYSQLSERVERDSN =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENERMYSQLSERVERDSN=
(SID_LIST=
(SID_DESC=
(SID_NAME=MYSQLSERVERDSN)
(ORACLE_HOME = d:\oracle\product\10.1.0\db_1)
(PROGRAM=hsodbc)
)


D. Alter your tnsnames.ora file.

$ORACLE_HOME/network/admin/tnsnames.ora altered file:

MYSQLSERVERDSN =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=MYSQLSERVERDSN))
(HS=OK)
)


E. Start the new listener.ora.

C:\>lsnrctl start listenermysqlserverdsn


F. Validate the connection to your DSN

C:\>tnsping mysqlserverdsn


G. Create a Database Link within your Oracle Database to the SQL Serer database.

SQL> create database link mysqlserverdsn connect to sa identified by using 'MYSQLSERVERDSN';

You know have a DB Link to the SQL Server and can run queries on it.