topic Re: Data extract from MS Sql Server db to SAP in Application Development and Automation Discussions

Data extract from MS Sql Server db to SAP

former_member588855 — Mon, 25 Jul 2011 10:37:25 GMT

Hi Experts ,

Can some one guide me extracting Data from SQL server to SAP System .

We have a seperate Non SAP system with MS SQL Server database .

Now requirement is to get those data s into Our SAP system.

We will create a Ztable and a RFC function module for that .

We will not deal with the Non-SAP application program rather we want to get data from directly databse (SQL).

User will run a program from SAP end and it will fetch data from that database(SQL) .

What is the methodology for that??

Re: Data extract from MS Sql Server db to SAP

Former Member — Mon, 25 Jul 2011 10:43:40 GMT

Hi,

SAP provided one option to connect directly the other database tables and fetch data, that is DB connection.

To establish DB connection, need to do below activities:

1. Register TNS entry of the source DB(remote DB) in SAP DB level

2. Configure the connection in DBCO transaction.

Once DB connection is activated , remote data can be fetched from SAP using native SQL.

For more details refer below thread on native sql

http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/content.htm

Regards,

Chandra

Re: Data extract from MS Sql Server db to SAP

Former Member — Tue, 26 Jul 2011 07:49:52 GMT

Hi,

well, just let me give you some more detailed answer.

Start transaction dbco to get into the DB-connection GUI. There you can enter the IP etc. of your MSSQL server. Take a look on the exisitng one as a template. Name the connection by your own.

In your program you need to open the connection by using this code:

DATA: dbs TYPE dbcon-con_name.

dbs = 'servername'.

open connection to MSSQL DB

EXEC SQL.

CONNECT TO :dbs

ENDEXEC.

Use any SQL command by putting it between EXEC SQL. and ENDEXEC.

example on select command:

EXEC SQL.

select single from server_table where bla = 'a' into ls_blub

ENDEXEC.

don´t forget to disconnect:

EXEC SQL.

DISCONNECT :dbs

ENDEXEC.

and for error handling:

if sy-subrc NE 0.

SKIP.

WRITE: 'error reading ls_blub'.

ENDIF.

mandatory to have changes effect on:

CALL FUNCTION 'DB_COMMIT'.

I hope that this application-oriented information will help you to have it done.

Re: Data extract from MS Sql Server db to SAP

SuhaSaha — Tue, 26 Jul 2011 07:59:58 GMT

Hello,

Instead of using Native SQL, you should check the [ADBC classes|http://help.sap.com/abapdocu_702/en/abenadbc.htm] for retrieving data from external databases.

BR,

Suhas

Re: Data extract from MS Sql Server db to SAP

former_member588855 — Wed, 27 Jul 2011 06:17:09 GMT

Hi ,

Thank you all for your suggestions . I understood what needs to be done from ABAP side .

Now problem is coming from BASIS side . Coz we have UNIX platform in SAP whereas in SQL we have Windows platform.

Can anybody have any idea how to build up connection with that 2 different platform db. I mean before going to t-code

DBCO any other step should we have to follow??

Regards

Re: Data extract from MS Sql Server db to SAP

Former Member — Thu, 28 Jul 2011 04:55:20 GMT

Hi,

Please refer my previous answer..

You need to register sql db tns entry in SAP.. Please check with your DB admin

Thanks and Regards,

Chandra