INSERT…EXECUTE over linked servers

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

Ever tried the code-efficient, want-to-have function of running a remote stored procedure and insert the resultset in a local table?

I tried it today within my assignment as a SQL Server consultant at a big company that has loads of SQL Servers and therefore highly distributed data stores, for historical reasons. In SQL Server 2005 the INSERT…EXECUTE was not supported and caused an error. Happily I discovered that this will change in a month when the customer finally migrates to SQL Server 2008 R2. SQL Server 2008 R2 has not fully, but good enough support for this. Now you can make that happen in most cases. But only if you don’t have the MARS (multiple active result sets) enabled. Then it’s the “same old, same old” error occurring.

The difference with SQL Server 2008 is that the transaction semantics is changed for executing against a loopback linked server. I like it! Way to go Microsoft!