DevOps, CI/CD and Automation

Hi,

I am using SQL Server 2008 R2 and we have a linked server connection to Oracle 11g. We have a table on Oracle thats why we need to do some CRUD operations on both servers. I got this error while trying to update/insert in both servers in a transaction as follows. How can I fix this???

Best Regards.

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "***" was unable to begin a distributed transaction.

OLE DB provider "OraOLEDB.Oracle" for linked server "***" returned message "New transaction cannot enlist in the specified transaction coordinator. ".

Sample code:

...

conn.Open()
myTransaction = conn.BeginTransaction()

'This is ORACLE
Dim comm As New Data.SqlClient.SqlCommand("UPDATE [***]..[**].[***] SET ApprovedBy=@approvedBy,ApproveDate=@approvalDate,Approved=@approved, StatusId=@StatusId where RequestID=@requestID", conn)

'Add to Transaction
comm.Transaction = myTransaction

comm.Parameters.AddWithValue("@approvedBy", appBy)
comm.Parameters.AddWithValue("@approvalDate", appDate)
comm.Parameters.AddWithValue("@approved", app)
comm.Parameters.AddWithValue("@StatusId", status)
comm.Parameters.AddWithValue("@requestID", reqId)


comm.ExecuteNonQuery()

'This is SQL Server
Dim comm1 As New Data.SqlClient.SqlCommand("INSERT INTO Table1 (DeliveryNo,VendorId) VALUES(@DeliveryNo,@VendorId)", conn)

'Add to Transaction
comm1.Transaction = myTransaction
comm1.Parameters.AddWithValue("@DeliveryNo", deliveryID)
comm1.Parameters.AddWithValue("@ID", ID)

comm1.ExecuteNonQuery()

'Commit Transactions & Close
myTransaction.Commit()
conn.Close()