3 Replies Latest reply: Aug 4, 2011 1:00 PM by gdarling - oracle RSS

    Linked Server problem on Transaction Usage???


      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:


      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)


      '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)


      'Commit Transactions & Close