How to insert in table from remote stored procedure without creating a distributed transaction?

Question

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.

Answer 1

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=<server or ip>,1433;Network Library=DBMSSOCN;Initial Catalog=<database name>;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.