Errors when calling stored procedure in sql server ( 2005 ) from XI 3.0

[Former Member]

Hi

We are getting the following error when stored procedures are invoked on sql server 2005 - from XI 3.0 ( SP23 ). A bunch of stored procedures on the sql server are called from XI in a group ( one after the other ) as a single LUW. In other words, there is no transaction control done from within any of the stored procedures being called.

*****************************************************************************************************************

Delivery of the message to the application using connection JDBC_http://sap.com/xi/XI/System failed, due to: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'usp_ROES_LOAD_SAP_Material_Description' (structure 'STMT_MATERIAL_DESCRIPTION'): java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction..

*********************************************************************************************************************************************

In the jdbc receiver comm.channel :

transaction isolation = Serializable

DB Autocommit Enabled - not checked

Disconnect from db after processing each message - not checked

Batch mode - not checked

Has anyone experienced the above issue. Any thoughts/pointers on this problem ?

[former_member187339]

Hi Karthik,

>>A bunch of stored procedures on the sql server are called from XI in a group

Can ou paste here the target structure (which goes into DB). You can take this from SXMB_MONI payload (after message mapping).

Regards

Suraj

[Former Member]

Actually we don't have these error messages in our development environment.( when XI is pointing to our dev sql server ) Also, when these messages are retried in the next 5 minutes ( 5 minute retry interval ) in our qa environment, they go through succesful sometimes.

The payload is

**************************************************************************************

- <ns0:mt_ZMATMAS_MANAGE_ROES xmlns:ns0="http://loreal.com/smd/matmas">

- <STMT_MATERIAL>

- <usp_ROES_LOAD_SAP_MATERIAL action="EXECUTE">

<Material_Number_CD isinput="true" type="VARCHAR">192897</Material_Number_CD>

<Division_CD isinput="true" type="VARCHAR">22</Division_CD>

<Active_Ind isinput="true" type="CHAR">04</Active_Ind>

<Launch_Date isinput="true" type="VARCHAR">20040601</Launch_Date>

<Sales_Product_Group_CD isinput="true" type="VARCHAR">MATRIX</Sales_Product_Group_CD>

<International_Hierarchy isinput="true" type="VARCHAR">C604990100051XC299</International_Hierarchy>

<Change_Ind isinput="true" type="VARCHAR">004</Change_Ind>

</usp_ROES_LOAD_SAP_MATERIAL>

</STMT_MATERIAL>

- <STMT_MATERIAL_DESCRIPTION>

- <usp_ROES_LOAD_SAP_Material_Description action="EXECUTE">

<Material_Number_CD isInput="true" type="VARCHAR">192897</Material_Number_CD>

<Laguage_Key isInput="true" type="VARCHAR">E</Laguage_Key>

<English_Material_Desc isInput="true" type="VARCHAR">MX LC FORMULATION CENTER NEW 1</English_Material_Desc>

<Change_IND isInput="true" type="char">018</Change_IND>

</usp_ROES_LOAD_SAP_Material_Description>

</STMT_MATERIAL_DESCRIPTION>

</ns0:mt_ZMATMAS_MANAGE_ROES>

[former_member187339]

Hi KArthik,

Do you want the following order for processing

1. Execute usp_ROES_LOAD_SAP_MATERIAL

2. Then execute usp_ROES_LOAD_SAP_Material_Description ?

What will happen if usp_ROES_LOAD_SAP_Material_Description is executed before usp_ROES_LOAD_SAP_MATERIAL? Will there be any foreign key violation in Database? I think this is what happening here.. Because there is no guarantee that the order (usp_ROES_LOAD_SAP_MATERIAL and then usp_ROES_LOAD_SAP_Material_Description) will be maintained at the time of processing.

As a remedy I suggest you to call a single SP and from there maintain the order. (1 and then 2)

Regards

Suraj

[Former Member]

Suraj - sorry for the delayed response. My s-id in SDN expired somehow and I had to create a new userid.

The actual issue that were facing was because of setting the max.concurrency parameter in the communication channel for the JDBC receiver - as more than 1. This resulted in multiple instances of stored procedures ( when XI passes multiple calls for different materials simutaneously ) locking each other ( mostly because of select statement within stored procedure implementation selecting data from a common table without nolock ) . Once we set the max.concurrency to 1 , the error stopped occuring.

[former_member732072]

Hi Karthik,

Please have a look at the following link and see if it helps you

Best Regards