7

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.

  • Can you be more specific? You want to run a stored procedure on a remote server and use the output as the source for an insert into a local table, or what? – JNK Jul 17 '13 at 17:42
  • Yes, that's it... – Guillermo Gutiérrez Jul 17 '13 at 19:38
12

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.