Home Forums SQL Server 2005 SQL Server 2005 General Discussion OLE DB provider "SQLNCLI" for linked server " " returned message "No transaction is active." RE: OLE DB provider "SQLNCLI" for linked server " " returned message "No transaction is active."

  • Hi all,

    I've been getting the same error message trying to fill a local table variable with the results of a remote stored procedure. Local database is SS2008 R2 on Win2008, and remote is SS2005 on Win2003 and in a different (trusted) domain.

    I set XACT_ABORT to off; and that didn't resolve it.

    I followed the instructions in the linked articles and set DTC security to "No Authentication Required"; and that didn't resolve it.

    I tested the linked server by running a direct query against a test table; and that ran fine. So there isn't a problem with the network, linked server settings, or security between the domains. It just seems to be with the INSERT INTO.... EXEC part.

    As a workaround I have re-written part of the remote sproc to fill an output varchar parameter with the same result set but using FOR XML. I then shred the xml in the local sproc. This seems to solve my immediate problem.

    Thanks for the above links anyway! They helped 🙂

    John