Skip to Content

JDBC Receiver action SET XACT_ON EXECUTE

Hi

Can any one tell me if/how this is possible please.

We have some JDBC interfaces.

The third party systems are being upgraded to SQL Server 2016 databases, which our current SAP versions do not support. We are upgrading SAP later this year. In the mean time our database team have configured a POC_interim database of version 2012.

SAP is to interface via the POC_interim database, which will in turn interface with the v2016 database. To do this, we have been changing our SQL statements in the JDBC adapter screen to say

On the JDBC Sender (inbound)

SET XACT_ABORT ON EXECUTE select_stored_procedure and

SET XACT_ABORT ON EXECUTE update_stored_procedure. They wouldn't work without "SET XACT_ABORT ON"

This now works fine.

I am trying to do the same on the JDBC Receiver (outbound) - the commands which are derived in the mapping

I have in the xml

spUpdate

action (attribute)

table (stored procedure name)

XMLinput

Field1

Field2

As it was, with just "EXECUTE" in the action, we got the error

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. "Storedprocedurename' (structure 'Statement'): com.microsoft.sqlserver.jdbc.SQLServerException: Unable to start a nested transaction for OLE DB provider "xxxxxxxx" for linked server "YYY\XXX" A nested transaction was required because the XACT_ABORT option was set to OFF.

Now with "SET XACT_ABORT ON EXECUTE" we get

Message processing failed. Cause: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Unsupported action attribute value 'SET XACT_ABORT ON EXECUTE' found in XML document

Is there any way of switching XACT_ABORT on? I have tried creating a constant prior to the action attribute in which I can put "SET XACT_ABORT ON", leaving just EXECUTE in the action attribute. But it won't let me. Perhaps the attribute needs to be at the top level.

Many thanks in advance.

Elizabeth

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    May 14 at 11:18 AM

    In fact I have now ticked the auto-commit-enabled box on the adapter advanced options tab, and removed any reference to SET XACT_ABORT ON. Things now work. Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • May 11 at 11:09 AM

    I have modified my mapping a little, and now have

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

    <ns0:MT_ Data_V2 xmlns:ns0="http://dddddd/data_Mobile">

    <Statement

    <spUpdateObjID>

    <setXact>SET XACT_ABORT ON </setXact>

    <executeSP action="EXECUTE">

    <table>StoredProc</table>

    <XmlInput type="VARCHAR" isInput="true"><n0:MT_ReadData xmlns:n0="http://DDD_Out_Mobile"><MobileDat>data for stored proc</MobileDat></n0:MT_yyyyyyyy_Data></XmlInput>

    </executeSP>

    </spUpdateObjID>

    </Statement>

    </ns0:MT_ Data_V2>

    Now I am getting

    Message processing failed. Cause: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: No 'action' attribute found in XML document (attribute "action" missing or wrong XML structure). Maybe I can't have commands before the Execute action. In the mean time I'll keep googling.

    Thanks Elizabeth

    Add comment
    10|10000 characters needed characters exceeded

  • May 11 at 11:44 AM

    I have also tried it again, with the executeSP (with action) element, up a level, outside of the spUpdateObjectID element, so that it becomes a level 3 hierarchy. As per info found stating that it needs to be at level three to work. Thanks Elizabeth

    Add comment
    10|10000 characters needed characters exceeded