topic Re: Transfer data from SAP to SQL server in Application Development and Automation Discussions

Transfer data from SAP to SQL server

Former Member — Tue, 03 Jun 2008 17:06:03 GMT

SAP Gurus,

I have a requirement to transfer data from SAP and pass it on to SQL server. Basically no middleware should be involved and data should be dumped instantly into SQL database. In other words, I will write a program to pull data and call a function(probably RFC and what ever mechanism) to instantly dump data in SQL database.

As I havent come across this requirement before, need your help in identifying various means of transfering data. Appreciate your replies as soon as possible.

Thanks

Vamsi

Re: Transfer data from SAP to SQL server

Former Member — Tue, 03 Jun 2008 17:26:04 GMT

Hello,

Instead of using a RFC you can use native SQL commands. Follow an example:


DATA: exc_ref    TYPE REF TO cx_sy_native_sql_error, 
      error_text TYPE string. 

TRY. 
    EXEC SQL. 
      INSERT INTO scarr 
                  (MANDT, CARRID, CARRNAME, CURRCODE, URL) 
        VALUES ('000', 'FF', 'Funny Flyers', 'EUR', 
                'http://www.ff.com'); 
      INSERT INTO scarr 
                 (MANDT, CARRID, CARRNAME, CURRCODE, URL) 
        VALUES ('000', 'EF', 'Easy Flyers', 'EUR', 
                'http://www.ef.com'); 
    ENDEXEC. 
  CATCH cx_sy_native_sql_error INTO exc_ref. 
    error_text = exc_ref->get_text( ). 
    MESSAGE error_text TYPE 'I'. 
ENDTRY.

Regards.

Re: Transfer data from SAP to SQL server

Former Member — Tue, 03 Jun 2008 17:33:34 GMT

Hi,

Thanks for your reply.

But I need to transfer data to a different SQL server database(there are tables on this server where I need to dump data pulled from SAP tables). But your command show that you are trying to insert data into SAP itself.

Thanks

Vamsi

Re: Transfer data from SAP to SQL server

Former Member — Tue, 03 Jun 2008 17:51:09 GMT

yeah, that's what I was wondering. the example looks like it's trying to insert data into SAP tables directly.. what about an external DB?

we use XI to transfer data to a SQL DB. I pass the data through an RFC to XI. they update the SQL DB. all happens asynchronously, but does happen almost immediately.

Re: Transfer data from SAP to SQL server

JanStallkamp — Tue, 03 Jun 2008 18:24:19 GMT

Hi Vamsi.

When I understand you right you want to write a report in ABAP that can store data in an MS-SQL database that is located on an other server than your SAP database. Is that right?

If yes I would suggest looking into ADBC (wimre that stands for ABAP DataBase Connector). There are some classes like CL_SQL_STATEMENT that allow this. One requirement is that you have installed the right DBSL (if your SAP system is running on a MS-SQL DB that is ok anyway) and. Then you have to maintain an database connection to the remote system. Transaction DBACOCKPIT is one way to do that.

As I'm posting that from home and not being in the office I don't have access to all documentation at the moment so I cannot go into detail here. And I fear there is not much documentation available in the public at all. But if you search for it you will find something.

But please remember one drawback of using ADBC (or EXEC-SQL or anything like that): You are writing native SQL that is executed on the database. ABAP syntax checks cannot help you at all. And you can do a lot of damage when accessing a database directly without good knowledge of what you are doing.

Hope that helps,

Jan Stallkamp

Re: Transfer data from SAP to SQL server

Former Member — Tue, 03 Jun 2008 21:48:47 GMT

Jan,

Thanks for your reply. Let me try in this direction.

Vamsi

Re: Transfer data from SAP to SQL server

GrahamRobbo — Tue, 03 Jun 2008 23:21:20 GMT

Hi Vamsi,

what you are after is called DB Multiconnect. Essentially you can connect directly from you ABAP WAS to an external database and use native SQL to read and write to the external database.

You need to install the db-specific portion of the SAP kernel unless you are running SAP on the same DB platform. You also need to install the db-client tools that facilitate remote access to the external database. Then maintain the db connection in transaction DBCON.

There are some OSS notes that cover how to set this up. Search for "db multi connect".

Cheers

Graham Robbo

Re: Transfer data from SAP to SQL server

Former Member — Wed, 23 Jul 2008 08:56:36 GMT

Dear Vamsi,

we also expect the exact requirement as you mentioned. We also need to transfer some data to SQL server from SAP. How you solve your issue? pls explain \[removed by moderator\]

i am expecting your reply in this regard

Sukumar

Edited by: Jan Stallkamp on Jul 23, 2008 10:58 AM

Re: Transfer data from SAP to SQL server

JanStallkamp — Wed, 23 Jul 2008 08:59:36 GMT

Hi.

Have you tried with DB multiconnect as mentioned before? If yes, what issues are you facing?

Best regards,

Jan Stallkamp

Re: Transfer data from SAP to SQL server

Former Member — Wed, 17 Sep 2008 10:25:33 GMT

Dear Guru's ,

I am facing same problem . Please see my details

I have done all the pre-requisites as per OSS note no. 178949 .

Details are as below :

Operating system : windows 2003 std edition 64 bit

Sap Kernal : 640

Dll file : dbmssslib.dll (64 Bit)

SAP : 4.7 version

While registering this file "regsvr32 dbmssslib.dll" on windows application server ,

it displays error dbmssslib.dll file was loaded ,but dllregisterServer entry point not

found.

How to load / register this file on windows application server . I have made entries in DCON .

I will very grateful to you . Please guide me .

Thanks in advance .

Re: Transfer data from SAP to SQL server

Former Member — Wed, 17 Sep 2008 12:26:28 GMT

Dear Friends ,

Cheers !!

I am thru . I installed Microsoft Data

Access Components (MDAC) and DBMSSLIB.DLL 32 bit instead of 64 bit.

Its working fine .

Thanks

Milind