Hello. I have a long query with different sections that I am tasked with converting to OpenQuery. I was able to convert all the sections and I have 9 temp tables created as a result. After I create all these temp tables through OpenQuery I can do a select statement on each one and see the data. My problem is this: there is a final report created at the very bottom and it references these temp tables through different Join statements. This final report is a dynamic OpenQuery. I have learned that I can't pass temp tables through this dynamic OpenQuery and I also cannot pass table variables through this dynamic OpenQuery. It seems that all I can pass through a dynamic OpenQuery are scalar variables. And I have not figured it how to put the data from a temp table into a scalar variable. I am not sure what I am asking is possible but I am also very green when it comes to OpenQuery. Any help would be appreciated. Thanks.
While the details are lacking, what you are attempting to do sounds very questionable. You should probably be doing this on the application server; I would look into using Entity Framework.
As for your specific question, I suspect you would need to convert the temp tables to either XML or JSON. ie If the temp tables are large this could consume a lot of memory.
You need to understand that using OPENQUERY is actually running the query on the destination server and not the local instance. Once that query has completed, the temporary tables no longer exist as that query has gone out of scope.
I would question the reasoning behind converting. If the original queries are using 4-part naming to query through a linked server - then the only reason to convert would be to ensure that those queries are only returning what is needed.
Having multiple separate queries that build temp tables that are then utilized by a 'dynamic' query doesn't see to be the best way to approach solving whatever problem you are trying to resolve here.
There are almost certainly better options - but we would need much more information to help.