Post reply

Linked Server error

  • July 29, 2011 at 4:11 pm

    #255018

    Hello All,

    I am executing a stored procedure that exists on a SQL Server 2008 server from a SQL Server 2005 server.

    I create the procedure on the 2008 server in a database called TestDatabase.

    CREATE PROCEDURE Test

    AS

    SELECT getdate()

    I call it from the 2005 server.

    EXEC LinkedServer.TestDatabase.dbo.Test

    This works.

    When I try to insert the contents of the stored procedure into a table on the 2005 server I get an error.

    CREATE TABLE Test1

    (TestDate datetime)

    INSERT INTO Test1

    EXEC LinkedServer.TestDatabase.dbo.Test

    OLE DB provider "SQLNCLI" for linked server "LinkedServer" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

    Msg 7391, Level 16, State 2, Line 1

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

    If I can execute the remote procedure why can't I insert the records into a local table? Is there any other way to accomplish this?

    Thank you all very much.

  • July 29, 2011 at 5:56 pm

    #1361174

    Please check the MSDTS, XACT_ABORT (SET XACT_ABORT ON), RPC were configured properly or not.

    Read this article, might be it will help you...

    http://ask.sqlservercentral.com/questions/17849/facing-msdtc-related-issues-while-archiving-data-from-one-database-server-machine-to-another-database-server-machine-using-windows-service-c

    Let us know what you find i n your case...

  • August 8, 2011 at 3:42 pm

    #1365563

    I was never able to resolve this and in the interim my specification has changed so I am no longer pursuing this.

    The following link seemed the most pertinent to my situation so I am posting this in case it can help someone else in the future.

    http://sqlserverplanet.com/sql/insert-stored-procedure-results-into-table/

  • August 8, 2011 at 4:15 pm

    #1365579

    MSDTC (MicroSoft Distributed Transaction Coordinator) sounds like it wasn't activated on the foreign system. Because you're inserting locally but the data is foreign it wants to open the transaction up across the servers.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 4 posts - 1 through 3 (of 3 total)

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