topic Re: ECC upgrade Native SQL statements in Application Development and Automation Discussions

ECC upgrade Native SQL statements

Former Member — Wed, 12 Dec 2012 13:05:16 GMT

hi ,

We have recently done our ECC upgrade from 4.6c.

There is an interface( abap program) which executes the SQL store procedures in another MS SQL database and transfers the data to it.

The connection is set up using DBCO transaction.

The native SQL statements transfers the data from SAP to SQL without an explicit commit in 4.6c.

The same program does not work in ECC unless we put an explicit commit statement.

There are around 200 programs like that..

Can you please let me know what is the solution for this ?... the one solution is modifying all 200 programs ... but it takes time...

is there any other solution or OSS note on this ?

Regards,

Chandan

Re: ECC upgrade Native SQL statements

SuhaSaha — Wed, 12 Dec 2012 15:17:38 GMT

Hello Raja,

If possible, please share the relevant portion of your code. This will help the forum member to analyse your problem.

BR,

Suhas

Re: ECC upgrade Native SQL statements

Former Member — Fri, 14 Dec 2012 11:51:06 GMT

hi suhas,

Please find below an example -

   FORM EXPORT_DATA.

FIELD-SYMBOLS:
    <wa_export_data>      LIKE LINE OF export_data.

"Connect to the FISO BO database.
"This connection must be defined in table DBCON using SM30.
"Connection info for DBCON:
"MSSQL_SERVER=<server name> MSSQL_DBNAME=<database name>
EXEC SQL.
    CONNECT TO 'FISO_BO' AS 'fiso_bo_conn'
ENDEXEC.

IF sy-subrc = 0.
    EXEC SQL.
      SET CONNECTION 'fiso_bo_conn'
    ENDEXEC.

    EXEC SQL.
      EXECUTE PROCEDURE dbo.sapTruncateTable (
        IN :'sapVendorsAccountingItems'
      )
    ENDEXEC.

    DESCRIBE TABLE export_data
      LINES export_count.

    LOOP AT export_data ASSIGNING <wa_export_data>.
      EXEC SQL.
        EXECUTE PROCEDURE dbo.sapInsertVendorsAccountingItems (
          IN :ploaddt,
          IN :<wa_export_data>-BUKRS,
          IN :<wa_export_data>-LIFNR,
          IN :<wa_export_data>-AUGDT,
          IN :<wa_export_data>-AUGBL,
          IN :<wa_export_data>-GJAHR,
          IN :<wa_export_data>-BELNR,
          IN :<wa_export_data>-BUZEI,
          IN :<wa_export_data>-BUDAT,
          IN :<wa_export_data>-BLDAT,
          IN :<wa_export_data>-WAERS,
          IN :<wa_export_data>-XBLNR,
          IN :<wa_export_data>-BLART,
          IN :<wa_export_data>-SHKZG,
          IN :<wa_export_data>-DMBTR,
          IN :<wa_export_data>-WRBTR,
          IN :<wa_export_data>-SGTXT,
          IN :<wa_export_data>-ZTERM,
          IN :<wa_export_data>-ZBD1T,
          IN :<wa_export_data>-STBLG,
          IN :<wa_export_data>-HKONT,
          IN :<wa_export_data>-BSTAT
        )
      ENDEXEC.

      PERFORM CHECK_BULK USING -1.
    ENDLOOP.

    EXEC SQL.
      DISCONNECT 'fiso_bo_conn'
    ENDEXEC.

    PERFORM SHOW_EXPORT_SUCCESS.
ELSE.
    MESSAGE E001.
ENDIF.

ENDFORM.                    " EXPORT_DAT

Re: ECC upgrade Native SQL statements

SuhaSaha — Mon, 17 Dec 2012 06:03:51 GMT

Hello Raja,

So where do you have to put your explicit COMMIT WORK to write the changes to the database?

BR,

Suhas

Re: ECC upgrade Native SQL statements

Former Member — Mon, 17 Dec 2012 10:57:03 GMT

hi Suhas,

the explicit commit is in subroutine

PERFORM CHECK_BULK USING -1.

The above program works fine in 4.6c but not in ECC.

In ECC i have to put explicit commit after ENDLOOP for the program to work.

Regards,

Chandan.

Re: ECC upgrade Native SQL statements

Jelena_Perfiljeva — Tue, 18 Dec 2012 19:30:17 GMT

Are you sure 4.6c and ECC are working with the same database on the other end? As I understand, Native SQL statements are pretty much just passed to the other database system. It seems rather odd that SAP version would make such difference...

I see that a stored procedure (?) is called as the last step. Maybe it has been modified? I'd also check in the target database - maybe there is some kind of log available to explain the difference.

We have only a few Native SQL programs and they do have commit, but I've never heard of this being a new requirement in ECC.