Update syntax in native SQL (MS SQL)

[George_Lioumis]

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

[Former Member]

Check the below links ,syntax will vary depend upon database.

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

http://www.itcserver.com/blog/2006/06/26/open-sql-vs-native-sql/

Thanks

Seshu

[Former Member]

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

[George_Lioumis]

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.

[Former Member]

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

[Former Member]

Hi Rob,

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

[Former Member]

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]

Hi George,

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

Raghuram.

[Former Member]

Hi George,

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