How to connect Oracle to SQL Server

How to connect Oracle to SQL Server?
=============================================
Here is an article I wrote on Hetergeneous Services.

In order to connect to SQL*Server from Oracle, you must do several things:

1. Install ODBC drivers for the SQL*Server database. The drivers are installed on your laptop or on the server that contains the Oracle code tree.

2. Setup the ODBC connection on your laptop using the Windows ODBC Data Source Administrator

3. Test the ODBC drivers to ensure that connectivity is made to the non-Oracle database.

4. Ensure the Global_names parameter is set to False in the Oracle database.

5. Configure the Hetergeneous services. This consists of creating an initodbc.ora file within the Oracle Oracle database installation.

7. Modify the Listener.ora file on the database installation to connect to the Oracle instance and ODBC drivers.

8. Modify the Tnsnames.ora file to point to the proper code tree.

9. Reload the listener

10. Create a database link on the Oracle installation.

11. Run a Select statement for the Oracle installation using the database link.


Indetails
=============

1 .
driver DG4ODBC supports Oracle 11g and Oracle 10g. Note that Oracle 10g needs a patch to work with DG4ODBC
(see http://www.oracle.com/technology/products/gateways/pdf/gateways_desupport.pdf). If you want to connect earlier versions of Oracle to non-Oracle systems through ODBC, refer to Accessing ODBC and JDBC Data Sources from Oracle Heterogeneous Services (HSODBC).

2.a) Create an Init File
------------------------
On the Oracle server, log in to the Oracle account and cd into $ORACLE_HOME/hs/admin. Every instance using DG4ODBC needs a separate init*.ora file. For this tutorial, we created an init file named inithsconnect.ora. You may want to name the init file to identify the target database. For example, inithsmssql.ora.
The init file specifies the:
• ODBC data source to connect to.
• Path of the ODBC driver manager shared object.
For example:
#
# This is a sample agent init file containing the HS parameters that
# are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MY_ODBC_DSN
HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so
# HS_FDS_TRACE_LEVEL = Debug
# HS_FDS_SUPPORT_STATISTICS = FALSE


2.b) Edit listener.ora
-------------------------
The Oracle Listener listens for incoming requests from the Oracle database. For the Oracle Listener to listen for DG4ODBC, information about DG4ODBC must be added to the Oracle Listener configuration file, listener.ora. You need to:
• Create a SID_NAME for DG4ODBC.
• Specify the executable that the listener should start in response to DG4ODBC connection requests.
listener.ora is located in $ORACLE_HOME/network/admin. Create a new SID_DESC within SID_LIST in the SID_LIST_LISTENER entry. For example:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(SID_NAME=hsconnect)
(ORACLE_HOME=/space/oracle/OraHome10db)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:
/usr/local/easysoft/lib)
)
)


2.c) Edit tnsnames.ora
The final Oracle file to edit is $ORACLE_HOME/network/admin/tnsnames.ora. You need to add an entry that identifies the Oracle server to attach to and the SID_NAME to use. For example:
hsconnectid=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521))
(CONNECT_DATA= (SID=hsconnect))
(HS=OK)
)


3. Start/Restart the listener
---------------------------------
You need to restart the listener so that it uses the new listener.ora entries. To do this:
cd $ORACLE_HOME/bin

./lsnrctl stop
./lsnrctl start
Note You may not have been running the Oracle listener service previously, but you need it to use DG4ODBC.
When you restart the listener, you may get information messages similar to:
Service "hsconnect" has 1 instance(s).
Instance "hsconnect", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
The status UNKNOWN is returned by DG4ODBC, as the service is not created until it is used.
You can also run lsnrctl and enter SERVICES to display service status information.
After adding the tnsnames alias and restarting the listener, use tnsping to check that you can connect to the new service. If tnsping succeeds, you will get a message similar to:
[oracle@fedora11 ~]$ tnsping hsconnect

TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 06-MAY-2008 17:19:20
Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
/home/oracle/2/product/11.1.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=fedora11)(PORT=1521))
(CONNECT_DATA= (SID=hsconnect)) (HS=OK))
OK (0 msec)
If tnsping successfully connects, you can now create a database instance and try to connect to it.


10. Create a Database Link
-----------------------------
Connect to SQL*Plus as if connecting to your regular database. At the SQL prompt, create a new database link:
CREATE PUBLIC DATABASE LINK hslink CONNECT TO
"dbuser" IDENTIFIED BY "dbpassword" using 'hsconnectid';
The previous command creates an Oracle database link named hslink that references the tnsnames.ora entry you created for DG4ODBC. Note that when creating the database link, the database user and password need to be supplied.
For SQL Server, "dbuser" and "dbpassword" must be a valid SQL Server login and password. For example, "mywindowsuser" and "mywindowspassword" if you normally connect to the instance with a Windows user account or "mysqlserveruser" and "mysqlserverpassword" if you normally connect with a SQL Server account.
'hsconnectid' is the tnsnames.ora entry you created to identify DG4ODBC and must be enclosed in single quotes. Otherwise, you get this error:
ERROR at line 1:
ORA-02010: missing host connect string
After creating the database link, try passing in an SQL statement:
select * from dual@hslink;
where "dual" is a table in your "hslink" linked database.
When passing in SQL to the newly created instance, the syntax is always:
table@linkname
You can even join linked database tables to those in your Oracle database:
select * from table@hslink,dual


Known Issues
===================================
SQL Server Unicode Data

If you get text corruption when retrieving nchar, nvarchar or ntext data through DG4ODBC:

1. Add these lines to your SQL Server ODBC driver data source in /etc/odbc.ini:
2. # Convert UCS-2 encoded Unicode data to UTF-8
3. ConvToUtf = Yes
4. # Also do this conversion for data passed to Unicode ODBC function (SQLxxxW) calls
ConvWToUtf = Yes
When these lines are present, the SQL Server ODBC driver converts UCS-2 encoded nchar/nvarchar/ntext data to UTF-8, which is the Unicode encoding scheme that DG4ODBC expects.

5. Restart your Oracle listener.
SQL Server varchar(max) Data
By default, the SQL Server ODBC driver exposes the varchar(max) data type as a SQL_VARCHAR. When reporting the maximum size of a varchar(max) column, the driver returns 0, which is the Microsoft convention for "unlimited". DG4ODBC is unable to interpret a zero length as an "unlimited" size and returns an error when retrieving varchar(max) data. To work around this:

1. Add this line to your SQL Server ODBC driver data source in /etc/odbc.ini:
VarMaxAsLong = Yes
When this line is present, the SQL Server ODBC driver returns a varchar(max) column as a SQL_LONGVARCHAR rather than a SQL_VARCHAR with a zero length.
2. Restart your Oracle listener.