How to store the results of a stored procedure without requiring a distributed transaction?
sql-server, sql-server-2008-r2
Solution
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.
Problem
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?