Search code examples
sql-servert-sqlexecutelinked-server

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

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'.

Solution

  • 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