Connect to Extenal SQL Server from SAP

[venkateswaran_k]

Dear All

I have set the following Configuration in order to Connect to External SQL Server database from SAP.

1. DBCO.

    I set DATABASE Connection :  <TDATA>

           DBMS                           :  MSS

           Username and Password:  <uid./<pwd>

           Connection informaltion  :   MSSQL_SERVER=<IP Address> SQL_DBNAME=<dbname>

2. I have written a connection ABAP program and tested.  It displays an error message as follows

   TRY .
      EXEC SQL .
        CONNECT TO 'TDATA'
      ENDEXEC.

       EXEC SQL.
        SET CONNECTION  'TDATA'
      ENDEXEC.

      EXEC SQL.
        SELECT COUNT(*) into :l_counter from <tablename>
      ENDEXEC.

      write : l_counter .

     CATCH cx_sy_native_sql_error INTO exc_ref.

        error_text = exc_ref->get_text( ).
        MESSAGE error_text TYPE 'I' RAISING sql_error.

ENDTRY.

It gives error on third EXEC SQL

   saying

You tried work with name of the table or view that does not exist in the databse.

However, my table name is correct.  i am able to view it in the database.

Kindly help me where I am missing.

Regards,

Venkat

[kesavadas_thekkillath]

If <tablename> is dynamic then the error is correct. Dynamic values are not allowed in native SQL.

Also check the table DBCON http://help.sap.com/abapdocu_70/en/ABAPEXEC_CONNECTION.htm

[venkateswaran_k]

Dear Kesav

the table name is not dynamic.  In fact it is  dbo.actatek_logs.

EXEC SQL.

        SELECT COUNT(*) into :l_counter from dbo.actatek_logs

ENDEXEC.

Regards,

Venkat

[SuhaSaha]

Hello Venkat,

As Kesav has mentioned you cannot use dynamic table names in Native SQL.

But imho Native SQL should be used (read: for connecting to external DBs) under exceptional conditions only (only one i can think of is your ABAP release doesn't have the ADBC interface). Especially given the fact that SAP has provided ABAP DB Connectivity(ADBC) interface.

It is more flexible to use conpared to Native SQL. And you can use dynamic access as well

BR,

Suhas

PS: Refer to the demo program ADBC_DEMO to see the usage of the ADBC classes!

[venkateswaran_k]

Dear Suhas

It is not dynamic table name.

In fact it is  dbo.actatek_logs.

EXEC SQL.

        SELECT COUNT(*) into :l_counter from dbo.actatek_logs

ENDEXEC.

Regards,

Venkat

[JanStallkamp]

Hi Venkat.

I'm not aware of all the MSS details but:

- some DB are case sensitive (e.g. Sybase ASE does this)

- is the user you are connecting with using the correct default database? (e.g. with Sybase ASE the user SAPSR3 will use the <SID>-database, whereas 'sapsa' will use the saptools-DB.

To check that the connection works at all I would suggest to run report ADBC_TEST_CONNECTION. That will ensure the basic concept is working.

Regards,

Jan

[former_member184473]

Hi Venkat,

Check the following notes and website for further information:

1601608 - How to access an external Microsoft SQL Server database

738371 - Creating DBCON multiconnect entries for SQL Server

323151 - Several DB connections with Native SQL

178949 - MSSQL: Database MultiConnect with EXEC SQL

http://help.sap.com/saphelp_nw70/helpdata/en/58/54f9c1562d104c9465dabd816f3f24/frameset.htm

Regards,

Eduardo Rezende

[Jelena]

I'm not really an expert on Native SQL, but we have a couple of programs that use it (with MS SQL Server) and the code is using cursor, it looks like this:

 
DATA: dbcur2 TYPE cursor.

EXEC SQL.
OPEN dbcur2 FOR
select ... from ... where ...
ENDEXEC.

Also, as already suggested, check if it's case sensitive. And just a wild guess - maybe the dot (.) in the name needs some special handling.