Skip to Content
-1

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

Oct 17, 2016 at 03:58 PM

601

avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Luis Darui
Oct 17, 2016 at 06:11 PM
1

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.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Dear Luis,

Thanks for the reply...

In your Code you mentioned ONE Table in the Select Statement. I think that table belongs to SQL Server. Same as i given in my code also, i mentioned SQL Table but whenever cursor comes to OPEN Statement Run time error is coming.

Like as below...

You are using object ( Table , view etc...) that is not present on DB.

The Current Error occurs in the DB Connection 'XYZ' .

Note : In my system MS SQL Server is not there. My Client System having MS SQL Server.

My Question is if we want to run the above code and fetch the data from SQL Server DB using ABAP into our system then, is our system should have the SQL Server also...?

If i run the same code in my Client's System is that run without getting above run time error and fetch the data..?

And one more thing what is T-SQL ?

Could you please give me steps for that if you think it would be solve my problem....

Thanks,

Vamsi

0
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.

0
Raymond Giuseppi
Oct 19, 2016 at 07:15 AM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Jelena Perfiljeva
Oct 18, 2016 at 07:00 PM
0

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.

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Jelena, It took me some days to review this thread, I'm still very attached to old SCN so I was expecting some alerts/notifications on this :(

Yes, I meant to use SQL Server Management Studio with SQL commands to import data instead programming in ABAP for it.

0

You're not the only one missing the notifications. :)

But OP seems to want to write the data into SAP tables. I doubt anyone would allow to write directly to the SAP tables from SQL . And we only know that OP's other DB is SQL Server, who knows what SAP runs on there. If the data was going from SAP into external DB then it may have been different, but still Basis people are usually not very keen on having SAP DB accessed directly.

1

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.

0
Vadim Kalinin Oct 18, 2016 at 07:06 PM
0

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

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hi Kalinin,

I am an ABAPer.

In DBCO who will be configure that connection process. Basis team or ABAPer.

Tell me the step by step process to Configure our MS SQL into DBCO.

The connection is done perfect or not we can check in SAP itself using

TCODE : DBACOCKPIT, i red this in one of SCN Form only....

In this tcode we can test SQL Connection using SQL Editor.

Navigation is as follows..

Tcode : DBACOCKPIT -----> Diagnostics-> SQL Editor .

There we write sql statements and if it works then that connection is perfect other wise not.

I don't know weather this is correct or not..

Tell me the process of configuration for SQL Server in DBCO.




Thanks ,

Vamsi.

0
vamsilakshman pendurti

I think your basis person has to correctly configure the system and test access to the required table in DBACOCPIT

0

If the connection was incorrect then most likely there would already be a dump at CONNECT TO... Although it is possible to have the right connection to the server but a wrong database or a user with wrong permissions.

Either way, such issues need to be investigated with the local Basis team and with the external DB owner. SCN can only be of very limited help here IMHO.

1

It's even better to test the table access with the specific user in ms sql studio.

Then in ABAP try very simple statement like select count (*) into :countvar from tablename

0
vamsilakshman pendurti Oct 30, 2016 at 07:37 AM
0

Thanks ,

I will ask this configuration details to our Basis Team.

Thanks,

Vamsi.

Share
10 |10000 characters needed characters left characters exceeded