Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


3
0
Sign in
to vote
Type: Bug
ID: 772113
Opened: 11/24/2012 7:10:12 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Several errors when have Show Actual Exection Plan On and trying to do
INSERT myTable
EXEC linkedserver.database.dbo.myProc
Details
Sign in to post a comment.
Posted by Microsoft on 2/4/2014 at 11:06 AM
Hello.
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
Hello,
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.

Thanks
Umachandar
Posted by Microsoft on 12/5/2012 at 3:44 PM
Hello,
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

Sign in to post a workaround.