Knowledge Article
Remedy - Server - Error when updating to a linked Oracle DB from ARSystem MSSQL database using Direct SQL action on a Filter
Remedy - Server - Error when updating to a linked Oracle DB from ARSystem MSSQL database using Direct SQL action on a Filter
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server was unable to begin a distributed transaction
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server was unable to begin a distributed transaction
Remedy AR System Server
AR System Server
BMC Remedy AR System Server versions 8.x, 9.x on MSSQL with a linked Oracle database.
While trying to perform an update using the following command from an ARS MSSQL database to a linked Oracle database an error is observed: Example of the update action being done: SET XACT_ABORT ON ; Update OPENQUERY(REMEDYORACLELNK, 'select * from MYGW.HD_REQUESTS WHERE request_id = $MYGW RequestID$') SET Remedy_ticket = '$Incident Ticket Number__c$', Remedy_status = '$Incident Status__c$' ; SET XACT_ABORT OFF The above SQL is being used on a Filter on an ARSystem installed against a MSSQL database but trying to use a DBLink to an Oracle database. The error observed is: "The SQL database operation failed.; The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "REMEDYORACLELNK" was unable to begin a distributed transaction." The error is recorded in the ARSystem SQL log as: <SQL > <TID: 0000000468> <RPC ID: 0000189751> <Queue: Fast > <Client-RPC: 390620 > <USER: username > <Overlay-Group: 1 > /* Mon Dec 28 2015 14:25:51.9850 */ org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state [S0002]; error code [7391]; The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "REMEDYORACLELNK" was unable to begin a distributed transaction.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "REMEDYORACLELNK" was unable to begin a distributed transaction. at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[na:na] |
Oracle client and MS SQL configuration related
This knowledge article may contain information that does not apply to version 21.05 or later which runs in a container environment. Please refer to Article Number 000385088 for more information about troubleshooting BMC products in containers. Note: This is not an actual problem with the ARSystem application and it will be required the DBA team assistance to check, test and perform the below steps. Check the following steps: 1- Install the latest Oracle client (12c) with lib files. Below is the link and the zip file. It contains an installer. http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html ---- ODAC121024_x64.zip - 287 MB (301,348,751 bytes) From there run the install.exe and accepted the defaults.
Enable the options:Oracle client binaries by default are installed under C:\app\client\Administrator\product\12.1.0. After installed these Oracle 12c libs and performed the steps mentioned below it should update the remote db. a) After installing Oracle client make changes to the OS Local DTC Properties in Component Services using steps below. A screenshot of the panel is shown below with the two options selected. Another options selected are already there by default. •Allow Remote Clients. •Allow Outbound. On Security tab of Local DTC Properties in Component Services. •Go To Run, type comexp.msc. •Double click "Console Root". •Double click "Component Services. •Double click "Computers". •Double click "My Computer". •Double click "Distributed Transaction Coordinator". •Right click "Local DTC" under "Distributed Transaction Coordinator", and click properties. •Click the "Security" tab. •Put tick marks on the checkboxes "Allow Remote Clients" and "Allow Outbound". b) After steps performed above restart SQL Server so that changes and Oracle client installation take effect.
|
Attachment(s):