User-1104215994 posted
Hi,
I am using SQL Server 2008 R2 and we have a linked server connection to Oracle 10g. 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()
