Post reply

Issue with the linked server query

  • April 20, 2009 at 10:36 am

    #130715

    Hi All,

    I have a Stored Procedure which has below statement.

    exec MyServer_linked.TestDB.dbo.USP_Core_Log_AddToAuditLog @system='HR Data Load', @eventString='updateTblContractorUser', @objectID=@peopleKey,

    When I run my SP, i get the below error message.

    OLE DB provider "SQLNCLI" for linked server "MyServer_linked" returned message "No transaction is active.".

    Msg 7391, Level 16, State 2, Procedure Usp_HRLoad_User_tblContractorUser, Line 98

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

    Note: SP was working fine 30 mins back and suddenly started giving issues in the linked server query. Please help me to fix this issue

    Regards,
    Suresh Arumugam

  • April 20, 2009 at 10:52 am

    #980879

    Did you add a BEGIN TRANSACTION somewhere?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • April 20, 2009 at 11:23 am

    #980907

    As Jeff was saying if you are using Transactions you will have to enabled MSDTC on the servers...

    Thanks.

    Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • April 20, 2009 at 11:43 am

    #980922

    Working with distributed transactions isn't always easy...

    I suppose you're logging something to the remote server, so the real question is: do you really need a distributed transaction to put a log entry? It depends. I usually work with SQL Server linked to AS/400 and the provider fails quite often: if it fails I have to reboot the machine to kill all the orphaned processes, that is not a choice for a mission-critical application.

    If you think you don't need the transaction, I would suggest calling the remote procedure after the COMMIT statement.

    Regards

    Gianluca

    -- Gianluca Sartori

  • April 21, 2009 at 5:16 am

    #981316

    Hi All,

    Thanks for your time & the replies.

    I have tried restarting both the linked servers and their MS Distributed Transaction Coordinator services. It has fixed the issues and now I could see my linked server query running.

    Thanks,

    Suresh

    Regards,
    Suresh Arumugam

  • April 21, 2009 at 9:09 am

    #981519

    I am happy that you got it working - thanks for the feedback.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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