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
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.