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