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.