Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
It seems to be available from SQL Server 2008 and above, but not for SQL Server 2005 :( (technet.microsoft.com/en-us/library/ms178532%28v=sql.90%29.aspx) –  Guillermo Gutiérrez Oct 21 '13 at 17:15
    
Answer updated for SQL Server 2005. –  Andomar Oct 21 '13 at 19:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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