topic Re: How to insert external SQL Table in ABAP? in Application Development and Automation Discussions

How to insert external SQL Table in ABAP?

Former Member — Fri, 21 Aug 2015 22:03:27 GMT

Hi Expert,

I try to insert a external SQL TABLE Like below.

And I try to insert this SQL TABLE by below abap program:

*Connect to DB

*Get connection

SELECT SINGLE text INTO i_con2 FROM zmmt66

WHERE name = 'CTO_REPORT'

AND con_txt1 = s_werks-low

AND seqno = '001'.

* checks if connection works

TRY.

IF i_con2 <> ''.

EXEC SQL.

SET CONNECTION :I_CON2

ENDEXEC.

ENDIF.

* retry again if failed.

IF sy-subrc <> 0.

EXEC SQL.

CONNECT TO :I_CON2 => Connecting is successful

ENDEXEC.

ENDIF.

CATCH cx_root INTO l_oref.

* force disconnect at any unexpected error

EXEC SQL.

DISCONNECT :I_CON2

ENDEXEC.

RETURN.

ENDTRY.

*Insert table

LOOP AT t_mocl2.

CLEAR: wa.

CLEAR: lv_time.

wa-zseqid = t_mocl2-zseqid.

wa-aufnr = t_mocl2-aufnr.

wa-matnr = t_mocl2-matnr.

wa-resbmatnr = t_mocl2-resbmatnr.

wa-menge = t_mocl2-menge.

wa-editor = 'SAPAUTO'.

t1 = sy-datum.

CONCATENATE sy-uzeit+0(2) ':' sy-uzeit+2(2) ':' sy-uzeit+4(2) INTO t2.

CONCATENATE t1 t2 INTO lv_time SEPARATED BY space.

wa-cdt = lv_time.

wa-udt = lv_time.

TRY.

EXEC SQL.

INSERT INTO [CTO_REPORT].[dbo].[SAP_PULL_LIST]

([Sequence No]

,[MO ]

,[SKU number]

,[IECPN]

,[Qty]

,[Editor]

,[Cdt]

,[Udt])

VALUES (:wa-zseqid,

:wa-aufnr,

:wa-matnr,

:wa-resbmatnr,

:wa-menge,

:wa-editor,

:wa-cdt,

:wa-udt)

ENDEXEC.

EXEC SQL.

Commit

ENDEXEC.

ENDTRY.

ENDLOOP.

But get below error msg,(Please click this PIC to see the error msg)

The insert data is like below

The question is I use same insert program in SQL to insert is ok.

Is any problem in my program,please kindly advise!

INSERT INTO [CTO_REPORT].[dbo].[SAP_PULL_LIST]

([Sequence No]

,[MO ]

,[SKU number]

,[IECPN]

,[Qty]

,[Editor]

,[Cdt]

,[Udt])

VALUES (:wa-zseqid,

:wa-aufnr,

:wa-matnr,

:wa-resbmatnr,

:wa-menge,

:wa-editor,

:wa-cdt,

:wa-udt)

Re: How to insert external SQL Table in ABAP?

Former Member — Sat, 22 Aug 2015 02:21:18 GMT

Hello Jack

Analyzing the error log, I guess there's an extra space in your second column name ([MO ]).

Try removing it. ([MO]).

Regards

Luis Becker

Re: How to insert external SQL Table in ABAP?

Former Member — Sat, 22 Aug 2015 03:19:14 GMT

Hi Becker,

After I remove the space and try again, But still got same error msg.

Re: How to insert external SQL Table in ABAP?

raphael_almeida — Sat, 22 Aug 2015 04:10:50 GMT

Jack,

You have tried to insert a line directly on sql console? If yes, it was successful?

Warm regards,

Raphael Pacheco.

Re: How to insert external SQL Table in ABAP?

Former Member — Sat, 22 Aug 2015 04:57:17 GMT

Hi Pacheco,

yes, I have insert in SQL successful.But not with variant.

I just copy the number into the values.

Re: How to insert external SQL Table in ABAP?

raphael_almeida — Sat, 22 Aug 2015 05:29:03 GMT

Fine...

First to all, you need to treat the exception on insertion oks?

Can you remove the "MO" column of the statement and try again?

One more thing, can you print the insert test I spoke above for analysis?

Warm regards,

Raphael Pacheco.

Re: How to insert external SQL Table in ABAP?

Former Member — Sat, 22 Aug 2015 08:34:45 GMT

Hi Pacheco,

It's work!!!! I can insert the database without MO.

Why MO can't use to insert?

Re: How to insert external SQL Table in ABAP?

Former Member — Sat, 22 Aug 2015 12:10:35 GMT

Can you check your table in the external database? It seems that the field does not exists in the table. The error message is from the external DB.

Try to do a select in the external table in ABAP and see the available fields.

Regards

Luis Becker

Re: How to insert external SQL Table in ABAP?

Former Member — Sat, 22 Aug 2015 22:16:25 GMT

Hi Becker,

I sure the database have the MO column, because I just get below insert to from SQL table.

I think is it possible SAP doesn't allow 'MO' as a column name?

INSERT INTO [CTO_REPORT].[dbo].[SAP_PULL_LIST]

([Sequence No]

,[MO ]

,[SKU number]

,[IECPN]

,[Qty]

,[Editor]

,[Cdt]

,[Udt])

VALUES

(<Sequence No, varchar(13),>

,<MO , varchar(12),>

,<SKU number, varchar(12),>

,<IECPN, varchar(12),>

,<Qty, int,>

,<Editor, varchar(20),>

,<Cdt, datetime,>

,<Udt, datetime,>)

Re: How to insert external SQL Table in ABAP?

Former Member — Tue, 25 Aug 2015 16:07:48 GMT

Hi Jack

Could you solve your issue?

I don't think 'MO' is not allowed as a column name, but analysing the insert statement you've sent it seems there's a whitespace in the original database table. So, it's possible that the database accepts this space, but the SAP system doesn't.

Can you check the table in the external database and change the column, removing the whitespace if it exists?

Regards

Luis Becker