How to store the results of a stored procedure without requiring a distributed transaction?

Question

I have a remote stored procedure that i am running:

EXECUTE Contoso.Frob.dbo.Grobber @StartDate='20140513', @EndDate='20140518'

and this remote stored procedure returns a rowset:

EmployeeID  EmployeeName    StartDateTime            EndDateTime
----------  --------------  -------------            -----------------------
619         Guyer, Kirsten  2014-05-13 19:00:00.000  2014-05-13 19:00:00.000
...

Excellent. Perfect. Good. Sweet.

Now that i have these results, i need to store them in a table. Any kind of table. I don't care what kind of table:

• physical table
• temporary table
• global temporary table
• table variable

I just need them stored so that i can process them. The problem is that when i try to insert the results into a table, whether it be:

• a physical table

  INSERT INTO EmployeeSchedule
  EXECUTE Contoso.Frob.dbo.Grobber @StartDate='20140513', @EndDate='20140518'
  

• temporary table

  INSERT INTO #EmployeeSchedule
  EXECUTE Contoso.Frob.dbo.Grobber @StartDate='20140513', @EndDate='20140518'
  

• a global temporary table

  INSERT INTO ##EmployeeSchedule
  EXECUTE Contoso.Frob.dbo.Grobber @StartDate='20140513', @EndDate='20140518'
  

• a table variable

  INSERT INTO @EmployeeSchedule
  EXECUTE Contoso.Frob.dbo.Grobber @StartDate='20140513', @EndDate='20140518'
  

SQL Server insists (nay, demands) that it begin a distributed transaction:

OLE DB provider "SQLNCLI10" for linked server "Contoso" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 41
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "Contoso" was unable to begin a distributed transaction.

Why not just...

Now, making changes to the Contoso server is not an option. Why? Doesn't matter. Pretend that Jack Bauer will make an appearance and Guantanamo anyone who tries to modify Contoso. This means i cannot enable or reconfigure MSDTC on \\Contoso.

Did you try using READ UNCOMMITTED?

Yes.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
INSERT INTO @EmployeeSchedule 
EXECUTE wclnightdb.NGDemo.dbo.tbtGetSchedule @StartDate, @EndDate 

The partner transaction manager has disabled its support for remote/network transactions.

And:

INSERT INTO #EmployeeSchedule 
WITH (NOLOCK)
EXECUTE wclnightdb.NGDemo.dbo.tbtGetSchedule @StartDate, @EndDate 

Sorry. No nolock. Nolock is a no no:

Msg 1065, Level 15, State 1, Line 15
The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.

I always could give up on SQL Server

If i were doing this in a programming environment, it would be fairly easy to fix:

using (IDataReader rdr = ADOHelper.Execute(conn, "EXECUTE Contoso.Frob.dbo.Grobber @StartDate='20140513', @EndDate='20140518'")
{
   while (rdr.Read())
   {
       InsertRowIntoTable(conn, rdr);
   }
}

Although that would require me to create a binary, ship it, and schedule it. I'm looking for the option that works with SQL Server (so SQL Agent can schedule the job).

Bonus Reading

• SET REMOTE_PROC_TRANSACTIONS (Transact-SQL)
• How do I use the results of a stored procedure from within another?
• How can one iterate over stored procedure results from within another stored procedure....without cursors?

Answer 1

SQL Server insists (nay, demands) that it begin a distributed transaction:

If you can't configure your servers to use distributed transactions for whatever reason, you can tell it not to.

USE [master]
GO

EXEC master.dbo.sp_serveroption 
    @server   = N'Contoso', 
    @optname  = N'remote proc transaction promotion', 
    @optvalue = N'false'
GO

Or in SSMS GUI:

I don't know all implications of turning off this option, but at least now my INSERT ... EXEC [LinkedServer]... works.