Post reply

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

  • September 4, 2007 at 4:01 pm

    #118385

    Hello!

    I have a sql server 2005 stored procedure that inserts a record into an oracle database table via a linked server.

    I can query the table and actually do inserts also, except for when implicit transactions is on.  When implicit_transactions is on I get : "The operation could not be performed because OLE DB provider "MSDAORA" for linked server was unable to begin a distributed transaction."  Because of the application that is calling the procedure I need implicit_transactions on.

    I've checked the security settings on my MSDTC, and changed them according to this thread:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=291577

    but this did not resolve my issue.

    I am running on windows server 2003, sql server 2005, and oracle 9i linked server database.

    Any help is greatly appreciated!! Thanks in Advance! Jen

     

  • September 5, 2007 at 1:11 am

    #731405

    Hi Jennifer Slack,

       Please check the below link to find solution for your problem,

              http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106

     1.  While adding your linked server you have enable the remote connection properties in Surface Area Configuration Tool.

     2.  Add that linked Server

     3.  Provide login credentials for the linked server and just try it out with simple select statement to find whether you can access the datas.

    Regards,

    Venkatesan Prabu. J

    HCL Technologies,

    Chennai

    Thanks and Regards,
    Venkatesan Prabu, 😛
    My Blog:

    http://venkattechnicalblog.blogspot.com/

  • September 5, 2007 at 7:33 am

    #731499

    Check name resolution to your linked Server. Saw very same problem with SQL Server 2000 and ended up being a DNS problem.

  • September 5, 2007 at 7:55 am

    #731509

    I have remote collation checked in the properties of the linked server object.

    I have a linked server login

    I can do simple selects and I can even do the insert statement through my stored procedure, until I turn implicit transactions on.  Then it no longer works.

    I can ping the linked server via name resolution.

    Thank you for your replies, let me know if this spawns any other thoughts....  Thanks!

  • September 5, 2007 at 8:46 am

    #731534

    You need to enable RPC Out (or was it RPC? - I always get those 2 mixed up) on the linked server object.  Are you able to do a simple select statement to the Oracle database (not using a SQL stored procedure)?  I've had those errors before due to a server hop issue also (I was querying one server to another on behalf of a second SQL server) but I'm assuming that's not your situation.

  • September 5, 2007 at 10:23 am

    #731572

    Are you inserting or updating?  I had an issue with updating that gave much the same error message, and it was because the table Iw as updating didn't have a Primary Key in it.

  • September 5, 2007 at 2:40 pm

    #731619

    I can do simple select statements from query analyzer. 

    I am inserting a record and not updating.

    I turned on RPC Out and RPC together and each alone... still get the same error. 🙁

    Thanks for the replies... not quite there yet.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply