Register Login

Fetch Data From SQL Server to SAP

Updated May 18, 2018

Hi Experts,

We have a requirement to connect to SQL Server from SAP and fetch data from it directly into SAP. We have tried with RFC call function destination statement. But later found that we can't use that for 'G' type of connections created using SM59. Should we change the type of connection or use some other methodology? Can anyone help us out?

Thank you


Comments

  • 16 Aug 2013 12:57 pm Sushma
    Hi,

    You could use native sql (run abaphelp with exec sql performing... and search for oss notes related to exec sql.)
  • 16 Aug 2013 12:57 pm Sushma
    First of all you should check whether your SAP is on Windows environment or
    not . If yes then you can connect through T.code : DBCO of sap by passing
    required parameters of SQL server. This connection can be used in any SAP
    program.

    Secondly, if the SAP is on other environment like HPUX or Solaris etc then
    it is a tough job like you need to install Windows Instance and then connect
    your SQL through that via T.Code mentioned above.

    Hope it works for you.
  • 16 Aug 2013 12:57 pm Sushma
    Hi,
    I presume the SQL server is another database. For that, you need to define the connection to this other database.
    A few hints (haven't used them myself, but I know of their existence)
    - check transaction DBACOCKPIT for setting up the connection to the other database.
    - table DBCON contains these connections.
    - Instead of EXEC SQL, you can also use ADBC classes.
    - as of the new WAS7* release (I think WAS731) there is also option to use native SQL with the addition CONNECTION.
  • 16 Aug 2013 12:57 pm amit butola
    Thanks for the answer but in order to use native SQL, we must use the connection created using DBCO right?
    What should we provide in conn info field for SQL Server connection in DBCO?
    I gave,
    MSSQL_SERVER=tcp:192.168.2.109 MSSQL_DBNAME=RFC OBJECT_SOURCE=DBO

    But I got the error,
    Synonyms missing: Please run initialization script as per SAP-note 706927
    in ST04.
  • 19 Aug 2013 6:05 am Sushma
    Hi,

    How are you actually doing your connection and from what? .NET? VB? What do your connection strings look like.

    I've written several scenarios using VBA from Microsoft Access where the code logs in using a normal user ID capable of executing an RFC, and calling a BAPI to get the data needed.
  • 19 Aug 2013 6:06 am Sushma
    Hi,

    I agree. I use Native SQL and DB Connection parameters are
    stored in table DBCON. Be sure that SAP servers (Apps and DB) have access
    to SQL Server server in your landscape (network rights..ping test).

    Here is an example on how to connect to SQL Server in table DBCON.

    Field DBCON-CON_ENV: MSSQL_SERVER=CLUSTERSQL3TEST MSSQL_DBNAME=dboCMSSAP

    Try to do it nicely in ABAP OO using classes. All DB access are
    encapsulated into methods so this way, other programmers can easily reuse
    without having to code Native SQL again and again. In fact, they just
    don't know what kind of DB they are accessing. All Native SQL code is
    hidden.
  • 19 Aug 2013 6:06 am Sushma
    You can still make the same calls into AIX without issue. Our original SAP world was Unix and the calls worked. When we switched all to Windows, all we really did was change server names. Again, I would suggest you use BAPI calls to simplify the work. If a BAPI does not exist, create one.
  • 19 Aug 2013 6:07 am Sushma
    As far as I remember, you'd need a windows App server. That's why I sent you to OSS notes. You'd have to work a bit with your basis team too. If you don't have a windows app server then you can go for a different approach. We've developed a program that creates and runs a vbscript for non oracle dbms accesses. BTW This approach is working fine since we were on 3.0f (looong time ago...)
  • 19 Aug 2013 6:07 am Sushma
    I need to be more specific. I would discourage direct access to SAP without the use of canned programs on the SAP side being executed by RFC. If RFC is used the processes are simple and can be executed without issue. You need much more work to execute SQL inside SAP.
  • 19 Aug 2013 6:08 am Sushma
    Let me see if I understand your situation correctly first: you are writing an ABAP program that needs to get data from an external database (I presume from one of your posts about DBCO that it is a Microsoft SQL Server database). If I have that right, here's the drill:
    1. Set up a user ID with SQL Server authentication in the external database. This user must, obviously, have access to the database you want to access from your ABAP program.
    2. In your SAP system, execute tcode DBCO. Create a new connection like so:
    DB Connection: whatever name you want to give it (this is the name you will use in your ABAP program). For the sake of this example we'll call it "MY_CONN".
    DBMS: MSS
    user name: the user you set up in step 1.
    DB password: the password you set up in step 1
    Conn. info: MSSQL_SERVER= MSSQL_DBNAME=
    Permanent: Checked
    Connection limit: 0
    Optimum connections: 0
    3. In your ABAP program, use SQL_EXEC statements to connect to, then access the external database. Here's some example code:

    * Connect to external database.
    EXEC SQL.
    set connection 'MY_CONN'
    ENDEXEC.
    IF sy-subrc <> 0.
    * If the connection isn't already open, open it now.
    EXEC SQL.
    connect to 'my_conn'
    ENDEXEC.
    IF sy-subrc <> 0.
    cp_subrc = sy-subrc.
    cp_msgtxt = 'Connection to external database failed'.
    EXIT.
    ENDIF.
    ENDIF.

    * Do a SELECT on a table in the external database.
    EXEC SQL.
    select * into :ls_struc_name from
    where key1 = :ls_other_struc
    ENDEXEC.

    The structure names prefixed with a colon are references to structures defined within the ABAP program; all other references are to aspects of the external database.
  • 19 Aug 2013 6:08 am amit butola
    Hi, Actually one way process is working. I mean, We have written an RFC
    function which .Net people are able to use to fetch the data using some
    code. We have created a 'G' type of connection in SM59. Now we need the
    reverse thing to happen. We should connect to the SQL Server and access a
    tables data into SAP..
  • 19 Aug 2013 6:12 am Sushma
    Same process only you want to use a BAPI that posts instead of retrieves. You will also probably have to use a COMMIT BAPI to save the transaction.

    Also be aware that depending on what you wish to post, you may have to use multiple RFC's to post to all tables needed.
  • 19 Aug 2013 6:12 am Sushma
    What part of SAP are you moving data into. That can be very disruptive.
  • 19 Aug 2013 6:13 am Sushma
    Whatever you do, absolutely do NOT try to insert records into tables without using a BAPI unless it is a custom "Z" table with no other dependencies. To do so could corrupt the database tables and give you referential integrity issues.
  • 19 Aug 2013 6:14 am amit butola
    Ya, I'll not directly place the fetched data into the tables. But fetching
    the data into internal table is the problem. We have tried many ways. Don't
    know where the problem is. Can you tell me what TNS Entry is for an SQL
    Server? Thanks.

×