Error Calling Stored Procedure Receiver adapter

[Former Member]

Hi Guys,

I am trying to send data to a stored procedure SQL server R2 2008 from SAP PI7.11.

Transport Protocol: JDBC2.0

Message Protocol : XMLSQLFORMAT

I am able to read from same database using sender adapter.

Name of SP: InderTest

Paramters in SP : @Carrier Varchar(20), @Location Varchar(20), @Product Varchar(20), @Zone Varchar(20),

@dteStart date, @dteEnd date, @strType Varchar(1), @intRate Float

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

- <ns0:MT_FreightPricing xmlns:ns0="http://na.holcim.com/pi/o2c/pricingmaster">

- <StatementName1>

- <InderTest action="EXECUTE">

<table>InderTest</table>

<Carrier isInput="true" type="VARCHAR">4000004</Carrier>

<Location isInput="true" type="VARCHAR">3600</Location>

<Product isInput="true" type="VARCHAR">3553</Product>

<Zone isInput="true" type="VARCHAR">Zone1</Zone>

<dteStart isInput="true" type="DATE">20110704</dteStart>

<dteEnd isInput="true" type="DATE">20120704</dteEnd>

<strType isInput="true" type="VARCHAR">A</strType>

<intRate isInput="true" type="FLOAT">10.00</intRate>

</InderTest>

</StatementName1>

</ns0:MT_FreightPricing>

Error in JDBC Receiver

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. 'InderTest' (structure 'StatementName1'): java.lang.IllegalArgumentException

Any clue...

I was reading this help document. They have mentioned 5 configuration step, i have worked on JDBC earlier but never did anything like that.... Any idea ?

http://help.sap.com/saphelp_nw70/helpdata/en/b0/676b3c255b1475e10000000a114084/content.htm

Regards

Inder

[Former Member · Accepted solution]

Hi Kulwinder,

According to your stored procedure request structure is fine.

The problem is in your data, Escipally "DATE"

Please check your 'Date' format of your database and pass the right format.

First try test with stored procedure manually if you authorized, If not contact your database guy and ask him test with the same data manually. Then you will know error easily on which system..

I hope this is your problem.

Thank you,

Sateesh

Edited by: sateesh kumar .N on Sep 30, 2011 12:52 PM

[Former Member · Accepted solution]

Just sent date as VARCHAR. it fixed the issue.

[Former Member · Accepted solution]

Yes, great!

I don't know the exactly reason, but there're some problem to call Oracle SP from SAP passing DATE parameters.

These problems occurs also in DBLink communication.

[Former Member]

As this is parsing error, can you check your JDBC receiver Message type?

--Divyesh

[Former Member]

<table> element is optional in the structure. In that case you can try:

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

<ns0:MT_FreightPricing xmlns:ns0="http://na.holcim.com/pi/o2c/pricingmaster">

<StatementName1>

<InderTest action="EXECUTE">

<Carrier isInput="true" type="VARCHAR">4000004</Carrier>

<Location isInput="true" type="VARCHAR">3600</Location>

<Product isInput="true" type="VARCHAR">3553</Product>

<Zone isInput="true" type="VARCHAR">Zone1</Zone>

<dteStart isInput="true" type="DATE">20110704</dteStart>

<dteEnd isInput="true" type="DATE">20120704</dteEnd>

<strType isInput="true" type="VARCHAR">A</strType>

<intRate isInput="true" type="FLOAT">10.00</intRate>

</InderTest>

</StatementName1>

</ns0:MT_FreightPricing>

Regards

Raj

[Former Member]

Hi,

In the field of receiver message <table>InderTest</table> try to call the Stored Procedure with FULL Name (<Package>.<StoredProcedureName>), something like this:

<table> PACKAGETEST.INDERTEST </table>

Check also if Structure's field type match the SP parameters type.

[Former Member]

Hi,

The error looks like the stored procedure not identified in DB. Can you check using <table>dbo.InderTest</table> table value. Also check Database how stored procedure name looks like.

Hope this helps!

Cheers

Veera