locked
Oracle Linked Server problem on Transaction usage??? RRS feed

  • Question

  • 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()
    Friday, July 29, 2011 3:52 AM

Answers

All replies