| Print |  E-mail
User Rating: / 3
PoorBest 

From Oracle to SQL Server using dg4odbc

How to setup dg4odbc

Oracle Gateway Products

References: (Doc ID 1083703.1)
Options for Connecting to Foreign Data Stores and Non-Oracle Databases.

 

This document is intended as lookup help to setup dg4odbc, test in TEST environment first. use on production environment is at your own risk.

Today:27-Sep-2011

win2008R264 is a Windows 2008 R2 64 bit.

Install using admin accout

Read this First:

Update: 08/30/2011:

Testing shows that using dg4odbc which is a free gateway, works fine when you want to do limited interactions between Oracle and SQL Server.

Select statements from Oracle to SQL Server and visa versa work fine, but some updates, delete or inserts might fail.

Also I have been testing Triggers on Oracle tables to initiate updates on remote SQL Server tables, which do not work. For this type of advanced interactions we must use a different gateway called DG4MSQL, which requires purchase of new licensing.

It would look something like this:

Oracle (Unix)--> Tnsnames.ora  (Unix)--> Dummy Listener on Windows Server where SQL server is running--> ODBC--> SQL Server.

On 64 bit environment:

Install SQL Server 64

Install Oracle 11gR2 64

Use 64 bit ODBC: located in c:\windows\system32\odbcad32.exe

VERY IMPORTANT Do not use 32 bit ODBC in c:\windows\syswow64\odbcad32.exe

 In SQL Server navigate to Security-->Logins and create a new login called idbasol password idbasol and make default database set to viewpoint and grants of sysadmin and public.

1) I first install full version for evaluation  of SQL Server on win2008R264

It installed successfully and created a sql server named after the host meaning my win2008R264

Using Sql Server management studio:

Create a user idbasol on the sql server

If you need to re-install SQL Server, then use control panel--> uninstall or remove programs and remove every thing which starts with Microsoft SQL Server. Also physically remove SQl Server from program files and program files (x86).

During install if you get an error about not being able to write of find this:

c:\users\username\app data\Microsoft_Corporation

Then just navigate there and delete Microsoft_Corporation in app data.

Instance name:  sqlserver

When starting Microsoft SQL Server management studio to logon to the sqlserver instance do as follow:

Server type: Database Engine

Server name: win2008R264 \SQLSLSERVER  (or just win2008R264 )

Authentification:            Windows Authentification

Or

Authentification:            SQL Server logon        and go as sa

2) Install Oracle 11gR2 64 bit on the windows server where SQL Server is running

do not create a database instance, you need just the binaries: This is to be used as Oracle Data Access (OraOLEDB.Oracle) by SQL Server 64 to create Link Servers going to Oracle.

 

3) Configure Oracle Listener:

You need an Oracle listener on the windows where you have SQL server.

If there is no Oracle database installed on the windows where you have the SQL server, then you need to install Oracle.

Using Oracle Net Configuration Assistant from the 11g R2 64 bit home; create and start a

listener and call it MSSQL in which you can insert the following entries which are required so the SQL server can be accessed from a remote database. Once you have an Oracle listener insert the following:

Location:          C:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN

File name          listner.ora

Insert the following: 

  (SID_LIST =

    (SID_DESC =

      (PROGRAM = dg4odbc)

      (SID_NAME = dg4odbc)

      (ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_1)

    )

 Your 64 bit Oracle listener.ora should look something like this:

MSSQL =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = win2008R264.idbasolutions.com)(PORT = 1521))

    )

  )

SID_LIST_MSSQL =

  (SID_LIST =

    (SID_DESC =

      (PROGRAM = dg4odbc)

      (SID_NAME = dg4odbc)

      (ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_1)

    )

  )

 

ADR_BASE_MSSQL = C:\Oracle

And bounce the Oracle listener from the Windows services, or command prompt.

Add this to the tnsnames.ora:

MSSQL =

      (DESCRIPTION=

        (ADDRESS=(PROTOCOL=tcp)

        (HOST=win2008R264.idbasolutions.com)

        (PORT=1521))

        (CONNECT_DATA=(SID=dg4odbc))

        (HS=OK)

      )

3) ODBC data source for SQL server

Navigate to Start--> Control Panel--> Administrative Tools--> Data Source (ODBC)

On a 64 bit Windows environment this usually points to c:\windows\system32\odbcad32.exe

which is a 64 bit ODBC, yes even though confusingly it says odbcad32.exe and it is in system32 it is actually the 64 bit ODBC.

To verify: Right click on DATA Source -->Properties and see where it points to.

Click System DSN Tab and add an ODBC connection to your target SQL server database if doesn’t already exist:

Click Add and select SQL Server:

Name it MSSQL and point it to sql server:

win2008R264

Choose driver SQL Server Native client

Flag Connect to SQL Server using idbasol (or sa)

Your local tnsnames.ora on host win2008R264  must already have an entry for your service name such as TEST or TRAIN in this example, otherwise Link Servers will not work. TEST or TRAIN being the remote Oracle instances you want to connect to from SQL Server or vise versa.

4) configure dg4odbc

On windows win2008R264  where you installed sql server

Navigate to C:\Oracle\product\11.2.0\dbhome_1\hs\admin

edit initdg4odbc.ora and save as initdg4dg4odbc.ora

SID being the one you defined in step 1 when creating a dummy listener for the sqlserver in the 11g home.

So initdg4odbc.ora and initdg4dg4odbc.ora should both have this:

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = MSSQL

HS_FDS_TRACE_LEVEL = off

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

5) tnsnames.ora on Oracle

On Unix where Oracle TEST is running add this to the end of oratest tnsnames.ora:

cd /u11/app/testdb/10.2.0/network/admin/TEST_win32xx

vi tnsnames.ora

MSSQL.idbasol.com =

      (DESCRIPTION=

        (ADDRESS=(PROTOCOL=tcp)

        (HOST=win2008R264.idbasolutions.com)

        (PORT=1521))

        (CONNECT_DATA=(SID=dg4odbc))

        (HS=OK)

      )

For testing in Oracle TEST logon as idbasol or an Oracle account and create following db link:

create database link MSSQL.idbasol.COM

connect to "idbasol" identified by "idbasol"

 USING 'MSSQL.idbasol.COM';

select * from CONTRACTTEST@MSSQL.idbasol.COM;

-->This worked fine and no error and displayed 42 rows.

Desupport info:

Metalink note 549796.1

Applies to: Oracle Database Gateway for SQL Server - Version: 9.2 to 10.2

All Oracle Transparent Gateways and Generic Connectivity are desupported from March 15th 2008.

The upgrade path is to use the equivalent 11g Database Gateway and the 11g Database Gateway for ODBC to replace Generic Connectivity.

11.1 - end of premier support August 2012

11.2 - end of premier support January 2015

 

Log files for the dummy 11g listener on windows 2008 64:

C:\Oracle\diag\tnslsnr\win2008R264 \MSSQL\alert

C:\Oracle\diag\tnslsnr\win2008R264 \MSSQL\trace

Convergence and stuff:

http://download.oracle.com/docs/html/B10544_01/apb.htm

Another special case involves session specific SQL functions such as USER,

USERENV and SYSDATE. These functions may need to be executed at the originating

site. A remote mapped statement containing these functions will contain a callback

link. For a non-Oracle database where callbacks are not supported this could (by

default) result in a restriction error.

For example, consider the following statement:

DELETE FROM emp@remote_db WHERE hiredate > sysdate;

The statement returns the following error message:

ORA-02070: database REMOTE_DB does not support special functions in this context

This often must be resolved by replacing special functions with a bind variable:

DELETE FROM emp@remote_db WHERE hiredate > :1;

If an unsupported function is used in a DELETE, INSERT, or UPDATE statement, it

generates this Oracle error:

ORA-02070: database db_link_name does not support function in this context

 

Download the Gateway:

In case the 11gR2 64 install does not install the latest g4odbc gateway, do as follow:

Click on the Patches and Updates tab in My Oracle Support. Put in patch number 10098816 and

Microsoft Windows x64 (64-bit) for the platform.

This is the 11.2.0.2 database patchset. The gateways distribution is the 5th file. You can

verify that by looking at the patch readme.

This file is the complete gateway cd. You only need to download this one file. When you run the setup, it will create the 11.2.0.2 oracle home

and install the gateway that you select while running the setup. Select the odbc gateway (dg4odbc).

Encountered errors:

1) ORA-02070: database MSSQL does not support TO_NUMBER in this context

UPDATE "JKL76"@MSSQL.idbasol.COM

SET

"TransDesc" = 'YOU ARE UPDATED'

WHERE "Equipment"='10101'

AND "GLAcct"='60220.0101'

AND "GLCo"='201';

 

1 row updated.

SQL> rollback;

Rollback complete.

But generates this error when run from Cobol:

"ORA-02070: database MSSQL does not support TO_NUMBER in this context. "

 

Solution or workaround:

You must use dg4mssql and not dg4odbc.

 

2) ORA-02070 - ROWID is not supported in this context

Getting this error from a select statement from Oracle Forms:

"ORA-02070 - ROWID is not supported in this context."

This is because Oracle Forms has an invisible ROWID field and when data is fetched from SQL Server table no Rowid is fetched since SQL Server table doesn't have one.

Solution or workaround:

You must use dg4mssql and not dg4odbc.

 

3) ORA-02047 is generated because you are trying to insert a record

UPDATE "JKL76"@MSSQL.idbasol.COM

SET

"TransDesc" = 'YOU ARE UPDATED'

WHERE "Equipment"='10101'

AND "GLAcct"='60220.0101'

AND "GLCo"='201';

 

When run From Cobol:

"ORA-02047: cannot join the distributed transaction in progress."

 Solution or workaround:

You must use dg4mssql and not dg4odbc.

Rewriting the update like this into a proc and then calling the proc from Cobol also fixed

the problem:

 

create or replace procedure p_update_JKL76 as

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

UPDATE "JKL76"@MSSQL.idbasol.COM

         SET

            "TransDesc" = 'YOU ARE UPDATED'

             WHERE "Equipment"  = '10101'

             AND "GLAcct"        = '60220.0101'

             AND "GLCo"        = '201';

Commit;

END;

/

4) ORA-28545: error diagnosed by Net8 when connecting to an agent

SQL select attempting to use the db link:
select * from dbo.Contracttest@MSSQL.idbasol.COM
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent Unable to retrieve text of NETWORK/NCR message
 
65535
ORA-02063: preceding 2 lines from MSSQL
Possible cause:This is caused by setting up 32 bit Oracle connecting to a 64 bit SQL Server using 64 bit ODBC.

 

5)  ORA-28545: error diagnosed by Net8 when connecting to an agent

SQL select attempting to use the db link:
select * from dbo.Contracttest@MSSQL.idbasol.COM

                               *
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent Unable to retrieve text of NETWORK/NCR message 
65535
ORA-02063: preceding 2 lines from MSSQL
Possible cause:This is caused by setting up 32 bit Oracle connecting to a 64 bit SQL Server using 64 bit ODBC.

6) DSN contains an architecture mismatch

SQL select attempting to use the db link:
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][Microsoft][ODBC Driver Manager] The specified
DSN contains an architecture mismatch between the Driver and Application (SQL
State: IM014; SQL Code: 0)
ORA-02063: preceding 2 lines from SQLSVRSD01
Possible cause:This is caused by setting up 32 bit Oracle connecting to a 64 bit SQL Server using 64 bit ODBC.

7) ORA-28511: lost RPC connection to heterogeneous
ORA-02068: following severe error from MSSQL
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SAC-DOTNET-01.idbasol.com)(PORT=1525
))(CONNECT_DATA=(SID=MSSQL)))
Possible cause:This one seems to be related to unrecognized data type which exist in SQL Server but the HSODBC 32 gateway cannot process.

Solution or workaround:

You must use dg4mssql and not dg4odbc.

8) Using trigger to update remote SQL table from Oracle:

ORA-06519: active autonomous transaction detected

ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "idbasol.T_VP_PROLES_I", line 16
ORA-04088: error during execution of trigger 'idbasol.T_VP_PROLES_I'

Solution or workaround:

You must use dg4mssql and not dg4odbc.

 

The error code ORA-02047 is generated because you are trying to insert a record using db link when the insert statement in Oracle has not been committed. Generic Connectivity meaning dg4odbc has some restrictions. One of them being "Generic Connectivity agents cannot participate in distributed transactions; they support single-site transactions only".

Solution or workaround:

You must use dg4mssql and not dg4odbc.

 

What we do:

idbasolutions makes software that allows easy navigation into database objects, sessions, storage, wait conditions, memory structures and more, all with drill-down sub-windows in a cascading and intuitive fashion.

If you like reading our papers, please take a moment and navigate to Product Demo  and lookup each modules presentations. Or navigate to Free Version Tab and download a demo copy of our software for you to keep and use at no charge. To buy please navigate to Buy tab to purchase a full copy.

Support documents such as how to install, system and database requirements and so on are available in Support tab.