Skip to Content
-1

how to access ms sql data using sap abap statement in sap?

Dear Experts ,

I want to import MS SQL Data into SAP ZTables using ABAP. Native SQL is one of the solution i got from SCN.

what kind of Basis Configuration is needed.

I used the code as above screen shot...

Whenever cursor comes to OPEN Statement DUMP is coming ...as

You are using Object ( Table,View,Index...etc ) that is not present on the database.

The Error occurred in the current DB Connection "XYZ" .

Can anybody solve this issue..

thanks,

vamsi.

sql1.jpg (47.8 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Oct 17, 2016 at 06:11 PM

    If you just want to load data into those tables makes much more sense to use T-SQL from SSMS instead of writing an ABAP code with Open SQL to load it, unless you have some kind of business logic or additional functions to do.

    What I did on my internal test system was the following code:

    REPORT Z_DUMMY_PROGRAM.
    EXEC SQL.
      CONNECT TO 'DUMMYCONNECTION'
    ENDEXEC.
    EXEC SQL.
      SET CONNECTION 'DUMMYCONNECTION'
    ENDEXEC.
    IF sy-subrc <> 0.
      RAISE EXCEPTION TYPE cx_sy_native_sql_error.
    ENDIF.
    
    EXEC SQL.
      OPEN C1 FOR
       SELECT * FROM DUMMYTABLE
    ENDEXEC.
    
    
    DO.
      EXEC SQL.
        FETCH NEXT C1
      ENDEXEC.
    ENDDO.

    This works fine, and I can even monitor the connection to the target database and the select being executed.

    Did you look at this ABAP development documentation?

    https://help.sap.com/abapdocu_70/en/ABAPEXEC_CONNECTION.htm

    I also think that your question is more towards to ABAP Development rather than to SQL Server expertise.

    Best regards,
    Luis

    Remark: if this answer your question, please mark it as helpful.

    Add comment
    10|10000 characters needed characters exceeded

    • Luis Darui vamsilakshman pendurti

      Hi Vamsi, I referenced a table in SQL Server that belongs to a SQL Server database connection, which I named "DUMMYCONNECTION". I have this connection set in DBCO. That is why I can access this table. If your DBCON to SQL Server is not working, then it is very likely your connection will fail. In this case, you have to configure your system to connect remotely to SQL Server with 1601608 (Raymond suggested this in his answer). If all of this is correct, I don't see a reason for this to fail.

      If you want to import this from an external SQL Server into your database (e.g. Oracle, IBM DB2, HANA, Sybase, MaxDB, etc), then it is better that you use then an ABAP program to run this integration, the scenario where you could use SQL Server Management Studio for this, would be if you have this also if both source and target database were a SQL Server database.

  • Oct 19, 2016 at 07:15 AM

    Read (you and admin as they have usually some work, installation of a client may be required, especially if your system work on another database, e.g. Oracle) the reference note is 1601608 - How to access an external Microsoft SQL Server database.

    Regards,
    Raymond

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 18, 2016 at 07:00 PM

    What is exactly not clear in the message text? "You are using Object ( Table,View,Index...etc ) that is not present on the database." seems rather self-explanatory. Whatever you're using does not exist in the database you are connecting to. From there it's quite logical to conclude that either you're connecting to a wrong database or you have a wrong object name. The latter need to be confirmed with the owner of the external DB.

    I'm not 100% sure what Luis meant, but I guess SSMS was MS SQL Server Management and the suggestion was, essentially, to do it the other way around. Perhaps Luis could clarify.

    T-SQL is in Wikipedia, look it up.

    Add comment
    10|10000 characters needed characters exceeded

    • I would say that depending on the goal of this he can achieve by importing it by using SSMS. Reading now that the system doesn't run on SQL Server, only the data he wants to import comes from SQL Server, the best is to use a DBCON to SQL Server then to import the data. Using openrowset for importing data between different databases can be very tricky.

  • Oct 18, 2016 at 07:06 PM

    May be you have incorrect setup of your connection in transaction DBCO?

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 30, 2016 at 07:37 AM

    Thanks ,

    I will ask this configuration details to our Basis Team.

    Thanks,

    Vamsi.

    Add comment
    10|10000 characters needed characters exceeded