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=<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
Enlist=false in the connection string should prevent distributed transactions.