topic Re: Update syntax in native SQL (MS SQL) in Application Development and Automation Discussions

Update syntax in native SQL (MS SQL)

George_Lioumis — Fri, 21 Sep 2007 13:58:42 GMT

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

Re: Update syntax in native SQL (MS SQL)

Former Member — Fri, 21 Sep 2007 14:22:12 GMT

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

Re: Update syntax in native SQL (MS SQL)

Former Member — Fri, 21 Sep 2007 14:24:05 GMT

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

Re: Update syntax in native SQL (MS SQL)

George_Lioumis — Fri, 19 Oct 2007 09:36:53 GMT

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.

Re: Update syntax in native SQL (MS SQL)

Former Member — Fri, 19 Oct 2007 13:45:44 GMT

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

Re: Update syntax in native SQL (MS SQL)

Former Member — Wed, 27 Feb 2008 12:24:18 GMT

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.

Re: Update syntax in native SQL (MS SQL)

Former Member — Thu, 03 Apr 2008 16:02:08 GMT

Hi George,

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

Raghuram.

Re: Update syntax in native SQL (MS SQL)

Former Member — Tue, 08 Dec 2009 16:07:30 GMT

Hi George,

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

Re: Update syntax in native SQL (MS SQL)

Former Member — Tue, 04 Feb 2014 08:24:35 GMT

Hi Rob,

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