How to run remote sproc via linked server and store results in temp table on a clustered server

I need to be able to run a remote sproc and store it's results in a temp table so that further processing can be done against the data. I can run the below exec statement on it's own just fine and get the data back, however, when trying to insert into the temp table, I get the following error msg:

OLE DB provider "SQLNCLI" for linked server "LinkedServerName" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 8 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "LinkedServerName" was unable to begin a distributed transaction.

I don't want to use a join because it is being extremely slow, so I thought I'd try selecting the data I need by calling a remote sproc into a temp table, then work with it that way.

I've tried following instructions here with no luck: http://sql-articles.com/blogs/linked-server-problem-windows-2003-sp1-setting-msdtc-security-configuration/ I believe the main problem is that the source server (where I'm running the below SQL) is a clustered server, and that I'm missing some setting for DTC. Any ideas?

--drop table #tmp

CREATE TABLE #tmp
(
    col1 int,
    col2 int
);

insert into #tmp (col1, col2)
exec [LinkedServerName].[RemoteDBName].dbo.remote_sproc '04/01/2011', 开发者_如何转开发'04/06/2011'

select * from #tmp

While I didn't find a way to use distributed transactions on a clustered server setup, I did find an alternative way to grab the data remotely using OPENROWSET. Performance wise, it seemed very similar to using a linked server and is working well in our production environment.

/*
-- run the following once to configure SQL server to use OPENROWSET...

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

*/

-- still need a table to store the result set in to work
-- with the data after we grab it...

declare @table table
(
    col1 int,
    col2 int
);

-- use openrowset instead of a linked server

insert into @table
select *
FROM OPENROWSET('SQLNCLI', 'Server=HOSTNAME;Uid=USERNAME;Pwd=PASSWORD',
     'EXEC DBName.dbo.sprocName ''Param1'', ''Param2''')

select * from @table