How to fix Msg 7390 - The requested operation could be performed

Question: I have a SQL Server Linked Server configured , pointing to an ODBC - accessing a MongoDB driver . The test connections all work OK - and no problems running an OPENQUERY select statement using the Linked Server. 

But when I attempt to run an EXECUTE AT , and attempt to INSERT the data into a #temp table - I get an error message:

declare @myStmt varchar(max)
set @myStmt = 'select _id from mytable'
CREATE TABLE #temp2
(_id varchar(50)
)
INSERT INTO #temp2
EXECUTE (@myStmt) AT my_linked_server

DROP TABLE #temp2

Msg 7390, Level 16, State 2, Line 6
The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "my_linked_server" does
not support the required transaction interface.

Answer:

Sometimes it's tricky to fix issues related to linked server - and distributed transactions. The first thing to do is to check your Linked Server Configuration. If you go into the Linked Server Properties - you'll notice one of the options is "Enable Promotion of Distribution Transactions for RPC". 

It may be set at True. Change it to False.

From the Microsoft documentation :

"'remote proc transaction promotion' is a new option on SQL Server 2008, which allows you to control whether or not you want to enlist remote stored procedure call in a distributed transaction. When this option is off (FALSE), the local transaction will not be promoted to distributed transaction. This is how we are able to separate outer and inner transactions in a "autonomous transaction" fashion."

USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'my_linked_server', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO

The reasoning of changing it to False - is you are using EXECUTE AT , which is acting as a passthrough to the target server , and attempting to pass data back locally . I've tried this meethod before and it's solve a similar issue 

Some extra reading 

How to fix Msg 7347 - linked server returned data that does not match expected data length for column

MS DTC and sys.dm_tran_active_transactions (SQL Server DBA)

SQL WAIT TYPE – DTC and how to reduce it (SQL Server DBA)