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