AJAX Google Translator

| Print |  E-mail
User Rating: / 5
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.

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.

 

Overview of steps:

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

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

3) Configure Oracle Listener:

3) ODBC data source for SQL server

4) configure dg4odbc

5) tnsnames.ora on Oracle

 

If you need to setup DG4ODBC Oracle  Database Gateway for MS SQL Server, we can help.

Please click on About us, contact us tab and we will help you setup DG4ODBC or DG4MSQL, our fees for this work is $400 per node; meaning say you have a Unix server and a Windows server, then the cost is $800 and estimated completion is 8 to 15 hours.

For a description of our consultancy practice please click HERE.

 

 

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.

For a description of our consultancy practice please click HERE.

 

 

Comments   

 
0 #4 support@idbasolution 2013-04-05 07:20
You are welcome.
Please take a moment and download our free database tools, you will be pleased with how useful it can be with your day to day database navigation needs.
Quote
 
 
0 #3 lanpq 2013-04-04 22:20
Thanks!!!!!!!!! !!!!!!!
Quote
 
 
0 #2 support@idbasolution 2013-03-25 08:49
Mohamed, Glad that you found this helpful.
Quote
 
 
0 #1 Mohamed Amin 2013-03-23 03:27
Many Thanks for the effort did in this article.
Quote
 

Add comment


Security code
Refresh