How to insert in table from remote stored procedure without creating a distributed transaction? How can I insert in a local table from a remote stored procedure without creating a distributed transaction? I want to run a stored procedure on a remote server and use the output as the source for an insert into a local table.
For SQL Server 2008 and up, you can disable promotions for the linked server.  It's on the Advanced property tab, or you can script it like:

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




For SQL Server 2005, you can add an OLE DB linked server. That allows you to enter detailed options in the connection string.

Here's how to configure such a linked server.  Click Server Objects, then right click Linked Servers and choose New Linked Server....  Configure the server as follows:


Tab "General":

Linked server: enter name of linked server
Check Other data source
Select provider Microsoft OLE DB Provider for SQL Server
Product name: Enter anything here, like ZzzZZzzzz
Enter provider string
Data Source=&lt;server or ip&gt;,1433;Network Library=DBMSSOCN;Initial Catalog=&lt;database name&gt;;Enlist=false;

Tab "Security":

Choose Be made using this security context
Enter username and password



The Enlist=false in the connection string should prevent distributed transactions.