topic Re: Connecting to external database using ABAP in Application Development and Automation Discussions

Connecting to external database using ABAP

ramesh_putta — Tue, 25 Feb 2014 21:32:37 GMT

Hello All,

I am trying to connect to an external oracle database within the same network by doing the following..

- I setup the database connection using transaction code DMCO.

- in SE38 i used the following statement to connect to the database.

PARAMETERS dbs TYPE dbcon-con_name.

     EXEC SQL.
        CONNECT TO :dbs
      ENDEXEC.

Here i get the sy-subrc value as 4. not sure what am i doing wrong here.

Appriciate any help.

Re: Connecting to external database using ABAP

Former Member — Tue, 25 Feb 2014 21:48:01 GMT

Hello Ramesh,

Look at this help: http://help.sap.com/abapdocu_70/en/ABAPEXEC_CONNECTION.htm

If its not working properly maybe can be a firewall problem.

Re: Connecting to external database using ABAP

ramesh_putta — Wed, 26 Feb 2014 18:22:08 GMT

i Actually copied program code from the link you sent. but it still not working. Is there any other way we can test if the database connection is working with the parameters provided in DBCO? I am in the same network as of the oracle database so i do not think its the firewall problem.
I get sy-subrc = 4 after line "CONNECT TO :dbs" below

Here is the code ... PARAMETERS dbs TYPE dbcon-con_name.
DATA carrid_wa TYPE scarr-carrid.
DATA dbtype TYPE dbcon_dbms.
SELECT SINGLE dbms
   FROM dbcon
   INTO dbtype
   WHERE con_name = dbs.

IF dbtype = 'ORA'.
TRY.
   EXEC SQL.
CONNECT TO :dbs
   ENDEXEC.
   IF sy-subrc <> 0.
   RAISE EXCEPTION TYPE cx_sy_native_sql_error.
   ENDIF.
   EXEC SQL.
   OPEN dbcur FOR
   SELECT carrid
   FROM scarr
   ENDEXEC.   DO.
   EXEC SQL.
   FETCH NEXT dbcur INTO :carrid_wa
   ENDEXEC.
   IF sy-subrc <> 0.
   EXIT.
   ELSE.
   WRITE / carrid_wa.
   ENDIF.
   ENDDO.
   EXEC SQL.
   CLOSE dbcur
   ENDEXEC.
   EXEC SQL.
DISCONNECT :dbs
   ENDEXEC.
   CATCH cx_sy_native_sql_error.
   MESSAGE `Error in Native SQL.` TYPE 'I'.
ENDTRY.
ENDIF.

Re: Connecting to external database using ABAP

Former Member — Wed, 26 Feb 2014 18:24:15 GMT

Try run the report ADBC_TEST_CONNECTION

Re: Connecting to external database using ABAP

Former Member — Wed, 26 Feb 2014 18:30:16 GMT

Look at SAP Note 738371 too.

1. Always test your connection using either osql or the Query Analyzer from all application servers that may be used. Testing with "SQL Server Authentication" is easy. Using "Windows Authentication" is a bit more difficult. Often you cannot logon as NT user DOMAIN\SAPServiceSID. But usually user DOMAIN\sidadm has the same privileges, and therefore you can test by logging in as sidadm, and then execute

osql -E -S<server_name>

Make sure <server_name> matches the MSSQL_SERVER setting, including the prefix (except the empty : prefix). Once you are able to connect enter "use <dbname>" where dbname matches the MSSQL_DBNAME setting.

2. You can test a connection using the following abap test program:

REPORT ZTESTDBCON.

data: DBN(128).

EXEC SQL.

CONNECT TO 'TST'

ENDEXEC.

EXEC SQL.

SET CONNECTION 'TST'

ENDEXEC.

EXEC SQL.

SELECT db_name() INTO :DBN FROM SVERS

ENDEXEC.

WRITE: / 'current database name', DBN.

Substitute TST by your DBCON name (the CON_NAME from DBCON). If SVERS doesn't exist in your database, then use any table name that does exist.This will allow you to easily monitor errors when connecting.

3. If a connection fails or takes a very long time, the first place to look for errors is in the developer trace files (SID\DVEBMGS00\work\dev_w??). The error messages will indicate which network protocol was attempted:

[DBNMPNTW]ConnectionOpen

for example means that named pipes were being used. However the trace files don't show a lot of detail beyond that. To get a more detailed trace you can do the following:

a) se38 -> Run program RSMSS_DBSL_PROFILE_SWITCH. Choose to set profiling ON.

b) Run the program that connects to the remote server and wait for it to fail.

c) Run program RSMSS_DBSL_PROFILE_SWITCH again and turn profiling OFF. Never leave the profiling on for extended periods.

Now new trace file(s) will exist in SID\DVEBMGS00\work named dbsl_w??. These files contain much more detail about the server name, and other connect options being used.

Re: Connecting to external database using ABAP

Former Member — Wed, 26 Feb 2014 18:57:00 GMT

Thank you. I get following message when i run this tool.

Could not open connection MYCONNECTION .

sql error 12,514 occured:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I will look at the note you sent in below for more info.

Re: Connecting to external database using ABAP

Former Member — Wed, 26 Feb 2014 19:01:19 GMT

It looks like your DB is not correctly defined in the Listener definition file or service isn't up.

I found a lot of references to this error:

ORA-12514: TNS:listener does not currently know of service...

oracle - ORA-12514 TNS:listener does not currently know of service requested in connect descriptor - Stack Overflow

http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

Definelly your problem is in yout database service, not in SAP. If you don't have more questions, please close the thread.

Re: Connecting to external database using ABAP

ramesh_putta — Fri, 28 Feb 2014 16:48:57 GMT

I am not an expert on the oracle side , so not sure most of the thing mentioned in the SAP notes you suggested.

- Should the sql developer client already installed on my machine?

- And the SAP notes were talking about the ORA file. do we need to have this file copied into SAP application server ?

Please explain as i have limited knowledge on oracle side.

Re: Connecting to external database using ABAP

Former Member — Fri, 28 Feb 2014 17:19:41 GMT

Basically it's looks like your DB is not correctly defined in the Listener definition file or service isn't up.

Forgot about the SAP note, contact your DBA and inform that your application can't connect to the listerner (use this error code as reference: "ORA-12514").