November 24, 2012 at 5:52 pm
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!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 24, 2012 at 6:03 pm
Repros for me too. Definitely seems to be a bug.
November 24, 2012 at 8:30 pm
I am pretty certain that this is the same bug reported by Aaron Bertrand here:
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
November 24, 2012 at 8:51 pm
Agreed. Probably is the same.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply