How to get the result sets from tsql's execute?

Question

I try to remotely query table-valued function as it is offered in this SO answer.

But I stumbled over how to get the returned result sets to further work with them in sql code...

Calling UDF remotely is not supported by SQL Server and openquery cannot have parameters - only static string.

declare @query nvarchar(max) = 'select * into #workingDays from openquery(LNKDSRV, ''select * from DB.dbo.fxn_getWorkingDays('''''
    + cast(@date1 as nvarchar(max))
    + ''''',''''' 
    + cast(@date2 as nvarchar(max))
    + ''''')'')';
exec sys.sp_executesql @query;

When #workinDays is later queried there is a error 'invalid object name'.

Any comment by the voter to close? What is wrong with this question?! — Pavel Voronin, Dec 17 '14 at 14:35
define "get". return results to a client? store in a temp table? — Jeremy, Dec 17 '14 at 14:58
@Jeremy 'to further work with them in sql code...' temp table is a variant. will try exec + openquery — Pavel Voronin, Dec 17 '14 at 15:16
http://stackoverflow.com/questions/7126382/stored-procedure-and-populating-a-temp-table-from-a-linked-stored-procedure-with — Jeremy, Dec 17 '14 at 15:18

score 1 · Accepted Answer · answered Dec 18 '14 at 08:57

1

You have to define your table before sp_executesql to be available in the session:

Create table #tbl  
declare @query nvarchar(max) = 'insert into #tbl select * from....
exec sys.sp_executesql @query
select * from #tbl

Another option is to use global temp table ##tbl

answered Dec 18 '14 at 08:57

RAS

3,375
15
24

Yes, you are right. I was mistakenly thinking that 'select * into #tempTable' when run with sp_executesql will create temp table in my scope. – Pavel Voronin Dec 18 '14 at 09:12

How to get the result sets from tsql's execute?

1 Answers1

Linked