cancel
Showing results for 
Search instead for 
Did you mean: 

Error while inserting the data in MS SQL Database using stored procedure in receiver JDBC structure

former_member6134
Participant
0 Kudos

Dear Experts,

     I am trying to use stored procedure in the JDBC receiver scenario. When I am firing the scenario from the R/3 server using client proxy I am getting a clean transaction with chequered flag in SXMB_MONI. But when I am checking in RWB I am getting the following error:

 

  I have mentioned the name of the stored procedure in the TABLE field and EXECUTE in the action field in the receiver JDBC structure. The input payload generated is as follows:

<?xml version="1.0" encoding="utf-8" ?>

- <n0:MT_R3MSSQL_PRXY xmlns:n0="http://bccl.com/pi/R3MSSQL" xmlns:prx="urn:sap.com:proxy:PPR:/1SAI/TASB3581579EF8F3D728B29:701:2011/07/20">

- <DATA>

<BUKRS>1000</BUKRS>

<REP_FORM>MIS</REP_FORM>

<PABRJ>2011</PABRJ>

<PABRP>003</PABRP>

<REP_NAME>BRANCH PERFORMANCE</REP_NAME>

<GRP1>MYSORE</GRP1>

<GRP2>TOI</GRP2>

<SRNO>044</SRNO>

<ENAME>Other Direct AFC</ENAME>

<CY_FTM>6672.00</CY_FTM>

<PY_FTM>12545.00</PY_FTM>

<VAR_FTM>-46.82</VAR_FTM>

<BUD_FTM>12538.13</BUD_FTM>

<CY_VAR_F>-46.79</CY_VAR_F>

<CY_YTD>26501.00</CY_YTD>

<PY_YTD>39104.00</PY_YTD>

<VAR_YTD>-32.23</VAR_YTD>

<BUD_YTD>37614.39</BUD_YTD>

<CY_VAR_Y>-29.55</CY_VAR_Y>
</DATA>

</n0:MT_R3MSSQL_PRXY>

Can somebody guide how to overcome the error which I am getting while inserting the data in the database.

Thanking You

Regards

Suhas

Message processing failed. Cause: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'dbo.ZFT_MIS_DATA_BRANCH_KP_modify' (structure 'STATEMENT'): java.sql.SQLException: ERROR: Invalid XML document format for stored procedure: 'type="<SQL-type>"' attribute is missing for element 'access' (Setting a SQL-type (e.g. INTEGER, CHAR, DATE etc.) is mandatory !)

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello,

Can you paste your target structure? Your SP structure should look like below format, so you can cross check the same?

  <StatementName5>

<storedProcedureName action=” EXECUTE”>

<table>realStoredProcedureeName</table>

<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName > 

  </StatementName5>


http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

BTW, your error suggest that you are missing out some of the attributes values in the SP structure.

Thanks

Amit Srivastava

former_member6134
Participant
0 Kudos

Hi Amit,

    As asked by you the target stuctutre is as follows:

<?xml version="1.0" encoding="UTF-8" ?> 

- <ns0:MT_R3MSSQL_PRXY_REC xmlns:ns0="http://bccl.com/pi/R3MSSQL">

- <STATEMENT>

- <dbTableName action="EXECUTE">

<TABLE>dbo.ZFT_MIS_DATA_BRANCH_KP_modify</TABLE>

- <access>

<Company_name>1000</Company_name>

<Report_Format>MIS</Report_Format>

<Fiscal_Year>2011</Fiscal_Year>

<Period>003</Period>

<Report_name>BRANCH PERFORMANCE</Report_name>

<Group1>MYSORE</Group1>

<Group2>TOI</Group2>

<Srno>044</Srno>

<Name>Other Direct AFC</Name>

<ACT_CY_FTM>6672.00</ACT_CY_FTM>

<ACT_PY_FTM>12545.00</ACT_PY_FTM>

<VAR_ACT_CY_PY_FTM>-46.82</VAR_ACT_CY_PY_FTM>

<BUD_FTM>12538.13</BUD_FTM>

<VAR_CY_BUD_FTM>-46.79</VAR_CY_BUD_FTM>

<ACT_CY_YTD>26501.00</ACT_CY_YTD>
<ACT_PY_YTD>39104.00</ACT_PY_YTD>

<VAR_ACT_CY_PY_YTD>-32.23</VAR_ACT_CY_PY_YTD>

<BUD_YTD>37614.39</BUD_YTD>

<VAR_CY_BUD_YTD>-29.55</VAR_CY_BUD_YTD>
</access>

</dbTableName>

</STATEMENT>

</ns0:MT_R3MSSQL_PRXY_REC>

Yes I too believe that some attribute is missing. Pls let me know what is that I am missing ?

Thanks

With Best Regards

Suhas

former_member6134
Participant
0 Kudos

Hi Amit,

Also I would like to know how to create the stroed procedure structure, as in normal practice I have created the structure using Data Type, Message Type and service Interface. I am not aware of how to define the char type. Pls guide me on this.

Thanks & Regards

Suhas

Former Member
ambrish_mishra
Active Contributor
0 Kudos

Hi,

If you require the sql statement generated by the JDBC adapter for any error analysis, you can set logSQLStatement to true in "advanced mode" section of the communication channel. This will cause the generated statement to show in details page of the audit.

Hope it helps!

Ambrish

former_member6134
Participant
0 Kudos

Hi Amit,

    I have implemented the SP structure as guided by you andhave also got the desired results. Thank you very much for your , help time and effort.

Regards

Suhas

Answers (1)

Answers (1)

iaki_vila
Active Contributor
0 Kudos

Hi,

As Amit say it will be easier to find the problem with the stored procedure call. You should check that all the parameters have a type, and put them in lower case, like:

    <storedProcedureName action="EXECUTE">

      <table>Example</table>

      <inputparam type="char">123</inputparam>

Regards