INSERT EXEC RemoteProc with Actual Execution Plan Causes Error(s) - by TheSQLGuru

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 772113 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 11/24/2012 7:10:12 PM
Access Restriction Public


Several errors when have Show Actual Exection Plan On and trying to do 
INSERT myTable 
EXEC linkedserver.database.dbo.myProc
Sign in to post a comment.
Posted by Microsoft on 2/4/2014 at 11:06 AM
Thanks for bringing this issue to our attention. This issue has been fixed in the next SQL2012 service pack, and we are evaluating our option for the next major release of SQL server.
Here's some details on the root cause.
"INSERT ... EXEC" works by redirecting output of "EXEC" into a target table. On the other hand, statistics xml is passed to the client as a secondary output, in addition to the primary output from SELECT call. When statistics xml is enabled during "INSERT ... EXEC", only primary output should go to the target table, and the secondary output should go to the client. Under certain conditions, however, we found the server may try to insert the secondary output (statistics xml) into the target table, which results in the observed error. There is no workaround other than disabling statistics xml. This issue does not exist for earlier version of SQL server, because it does not produce statistics xml at all.
I hope this clarifies a bit. Thanks.

-- Jay Choe, Microsoft SQL Server
Posted by Microsoft on 4/8/2013 at 11:12 AM
We have now fixed this issue for the next major version of SQL Server. We will evaluate if we can put the fix in a future service pack for SQL Server 2012.

Posted by Microsoft on 12/5/2012 at 3:44 PM
Thanks for reporting the issue. We have a bug in the INSERT..EXEC syntax that disallows the RESULT SETS clause. Additionally, there is also an issue with the STASTICS XML processing for DQ. We will investigate the issues and let you know what we find.

Umachandar, SQL Programmability Team