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

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

Closed

as Fixed

Type:	Bug
ID:	772113
Opened:	11/24/2012 7:10:12 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Several errors when have Show Actual Exection Plan On and trying to do
INSERT myTable
EXEC linkedserver.database.dbo.myProc

Details (expand)

English

SQL Server 2012 - Enterprise Edition

SQL Engine

Windows Server 2008 R2 Enterprise (SP1)

US English

On server A, create the following:

create proc remotetest3 (@a int)
as
set nocount on

select object_id
from sys.objects
where object_id < @a

RETURN
GO

On server B, make a linked server to server A, then run the following:

drop table #tmp
go

create table #tmp (object_id int)
go
insert #tmp
execute serverA.toolsdatabase.dbo.remotetest3 100

Works fine. Now enable Show Actual Execution Plan and rerun the above. I get this error:

Msg 8114, Level 16, State 1, Line 1
Error converting data type nvarchar(max) to int.

If you run just the execute above I get this:

(67 row(s) affected)

(1 row(s) affected)

That second rows affected part is odd. If you run this:

execute serverA.toolsdatabase.dbo.remotetest3 100
with result sets ((Object_id int not null))
I get 67 rows of output and then this message:

Msg 11535, Level 16, State 1, Line 1
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), and the statement tried to send more result sets than this.

Clearly the act of getting the actual execution plan is breaking a simple linked server remote execution INSERT mytable EXEC myremotesproc. Can others verify this? I don't have access to lower builds of SQL Server at the moment. Wonder if this has always been the case?

Oh, one more oddity while I am at it. This:

insert #tmp
execute serverA.toolsdatabase.dbo.remotetest3 100
with result sets ((Object_id int not null));

gets me this error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'SETS'.

But it works fine if you aren't trying to do the insert!!

see steps to reproduce

X64

0 attachments

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