Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Update syntax in native SQL (MS SQL)

glio_ad
Active Participant
0 Kudos

Hi all.

I have successfully SELECTed data from an external MS SQL database through ABAP code using native sql, however I cannot update back that table in MS SQL. The following code seems to succeed as sy-subrc returns 0 after the UPDATE's execution however nothing is actually updated in the external DB table. The entry that I am trying to update exists in the external table and no exception is raised. Might that be a problem of authorization? The user I use to connect to the external MS SQL DB has full access to the z_dummy table. (at least, that's what the admins told me....).

I would appreciate (and of course reward) any help.

TRY.

EXEC SQL.

update z_dummy set key1 = :itab-key1,

text1 = :itab-text1,

flag1 = :itab-flag1,

flag2 = :itab-flag2

where key1 = :itab-key1

ENDEXEC.

IF sy-subrc = 0.

WRITE:/ itab-key1, ' was updated.' COLOR COL_POSITIVE.

ELSE.

WRITE:/ itab-key1, ' update failed' COLOR COL_NEGATIVE.

ENDIF.

CATCH cx_sy_native_sql_error.

WRITE:/ itab-key1, ' update failed' COLOR COL_NEGATIVE.

EXIT.

ENDTRY.

Regards,

George

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Just a thought - try an explicit COMMIT WORK AND WAIT if the update is successful (SY-SUBRC = 0).

If that doesn't work, try putting it just before ENDEXEC.

Rob

8 REPLIES 8

Former Member

Former Member
0 Kudos

Just a thought - try an explicit COMMIT WORK AND WAIT if the update is successful (SY-SUBRC = 0).

If that doesn't work, try putting it just before ENDEXEC.

Rob

0 Kudos

Thanks Rob.

It finally worked using an explicit COMMIT WORK AND WAIT after the whole update process of the external DB. Actually, I am not yet quite sure why it worked (!!!) as, if I am not wrong, the MSSQL has not an explicit commit. When you send an update query, it just updates the table. Furthermore, I noticed that before explicitly sending the COMMIT WORK, the update was taking quite a long time to finish in the external DB. After writing the COMMIT WORK AND WAIT though, the statement was executed immediately and the external DB was updated at once! Anyway, problem solved and thanks a lot.

Full reward points!

Happy coding.

0 Kudos

Yeah - sometimes you just have to kick the can enough times until it goes where you want it.

Anyway - glad it worked out for you.

Rob

0 Kudos

Hi Rob,

Just want to thank you. You've been very helpful to my interface issue.

Former Member
0 Kudos

I have the same problem but at least you can update the external database. Me I cant update the database. I'm using native sql to access and update informix database from within abap but it gives me a short dumb. The error points to the UPDATE line on this following code. Is there somethig I'm doing wrong? Please help.

EXEC SQL.

UPDATE ar_transaction SET ar_process_dt = :'X'

WHERE process_seq_no = :input_itab-process_seq_no

and server_id = :input_itab-server_id

and process_cnt = :input_itab-process_cnt

and ar_process_dt = :''

ENDEXEC.

Former Member
0 Kudos

Hi George,

Could you pls send me the native MSSQL code using SELECT statement.

Raghuram.

Former Member
0 Kudos

Hi George,

Could you pls send me the native MSSQL code using SELECT statement.