How to connect to an external Oracle DB when running MSSQL

[wolfgang_haerle]

Hi,

I was wondering if someone could help me figuring out of how to make an external Oracle DB available to our SAP (ECC 6.0) system running on MSSQL?

I know there is the DBCON entry but where and how to I specify where the Oracle DB is located (server IP, etc.). I am familiar with tnsnames.ora (in the Oracle world), is there something similar on the SQL server side?

Any ideas, hints are greatly appreciated.

Wolfgang

[former_member193399 · Accepted solution]

which version of SQL Server you are using?

You can use Linked Server in SQL Server to connect to external database. This is available in both SQL Server 2000 and 2005. Then you can use the SQL Server connection from SAP to access these tables and other available database objects.

[wolfgang_haerle · Accepted solution]

Ramesh,

thanks for your reply. We are using SQL Server 2005. Do you have any documentation about this linked server concept or know where I can find it?

Thanks,

Wolfgang

[former_member193399 · Accepted solution]

SQL Server Books OnLine (BOL) contains comprehensive help and examples. You can invoke the BOL from SQL Server Management Studio (SSMS) - > Help ->How Do i. Select the "Index" tab (tabs appears bottom of the help window) and type in Linked Servers. And there is a full page description available on how to access oracle database from SQL Server 2005 using linked servers.

I hope this helps. Let me know if this works out.

Thanks

Ramesh

[Former Member]

-download dboraslib.dll - also refered as libdbsl. this one is located in the Oracle specific directory of your kernel version.

-download Oracle 10 Instant Client. service.sap.com/swcenter-3pmain -- oracle etc.

-extract both to the same directory where disp+work.exe is executed from.

-get sqlnet.ora and tnsnames.ora from the Oracle DB - Server and put it to a local directory

- define environment variable TNS_ADMIN to point to the directory where sqlnet.ora and tnsnames.ora are located.

- who will have probably to define also an environement variable NLS_LANG (mostly AMERICAN_AMERICA.WE8DEC - or whatever your database codepage is).

that's it

peter

[wolfgang_haerle]

Peter,

thanks so much for your help; this was exactly what I needed. I have found all the software components and installed them the way you described it. Unfortunately I am still not able to connect. Is there something like a 'tnsping' I could use to toubleshoot my settings? How should the DBCON entry look like?

Thanks so much for your help und schoene Gruesse nach good old Germany!

Wolfgang

[Former Member]

no, tnsping is unfortunately not part of the instance client.

do you have access to the se38 rsbdcos0 report?

run it and run command >

set tns_admin

second command:

type %tns_admin%\tnsnames.ora

to check if your system sees the right configuration.

what error-messages do you find in \usr\sap\SID\Dxx\work directory after trying to connect to the remote location?

regards

Peter

[Former Member]

Hi Wolfgang

The notes that you may find useful are

323151 Several DB connections with Native SQL

178949 MSSQL: Database MultiConnect with EXEC SQL

You have to install SQL Server client tools AND dbmssslib.dll on all R/3

application servers where you want to execute the ABAP, which is

performing the MULTICONNECT.The two notes above will help you to properly configure the Dbcon connection.

I hope this helps

Éamonn