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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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:
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