I have a specific query that executes a stored procedure on a remote linked server and inserts the results into a local temp table.
I don't want it to promote a remote procedure transaction but I can't re-configure the server.
Is there a query hint or some way to force it to not promote a remote procedure transactions?
Edit: The reason I ask is because I'm getting the following error when trying to execute my remote procedure call:
OLE DB provider "SQLNCLI11" for linked server "MyRemotelyLinkedServer" returned message "The transaction manager has disabled its support for remote/network transactions.". Msg 7391, Level 16, State 2, Procedure spMyRemoteProcedure, Line 64 [Batch Start Line 15] The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "MyRemotelyLinkedServer" was unable to begin a distributed transaction.
I know I can set "Enable Promotion of Distributed Transactions" to false on the Linked Server options to fix this, but I'm not allowed to make server option changes in this context, so I'm looking for a workaround.