How to extract dynamically data from another database in function with DBName as parameter?

Question

I have one main database (MAIN) and several client DBs (CLIENTDB). I need to start query in MAIN and to get data from CLIENTDB. The client DB can be found also on linked server. My problem is that I need to do this in a function with EXEC, so I can get SELECT * FROM SRV.CLIENTDB, but this in not allowed by SQL Server. Is there another way to do that?

I need to have code like inside the function:

DECLARE @sSRV AS VARCHAR(128) = 'SRV';
DECLARE @sDB AS VARCHAR(128) = 'CLIENTDB1';

EXEC('SELECT * FROM ' + sSRV + '.' + @sDB + '.MyTable');

I need a function, because I need to make joins with it inside my engine.

Answer 1

It is not possible to do this dynamically via EXEC, as IIRC you can't do that in a function. You can't use an ad-hoc distributed query via OPENROWSET (https://msdn.microsoft.com/en-us/library/ms187569.aspx) either as OPENROWSET explicitly requires its inputs including the query text to be string literals not more general string values (so you can't use an expression or variable).

The only ways I can think of involve either having one function per remote database or the CASE/IF suggestion from Marian's comment. You could pragmatically create such a function by cursoring through a view based in sys.servers and other DMVs to put the function together through string concatenation, then replace the old version with the new using a call to EXEC. You still need to run the generation procedure every time the list of linked servers changes, but at least it would just be one procedure call (or script/statement run) and not a more manual code change.

This all sounds very hacky though. Such a "code smell" would suggest a redesign is in order if you have such power!

Answer 2

I think this post answers the question well. Using

EXEC AT

https://www.mssqltips.com/sqlservertip/1757/dynamic-sql-execution-on-remote-sql-server-using-exec-at/

Here's a portion of the post:

--Script 1 : Create a linked server 
EXEC sp_addlinkedserver 'FARAWAYSERVER', 'SQL Server'
--Script 2 : Execute a simple SELECT statement on the linked server
EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer') AT                                  
[FARAWAYSERVER];
GO
--Script 3 : Executing multiple SELECT statements on linked server and     
--getting multiple resultsets
EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer;
SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.CustomerAddress;') AT    
[FARAWAYSERVER];
GO

Thanks,

Answer 3

Given this requirement:

I need a function, because I need to make joins with it inside my engine.

I would say that the requirement itself (that it needs to be a function) is misplaced given the stated goal (that the data needs to be joined with local tables and/or views). The issue is that TVFs (whether they be T-SQL or SQLCLR) behave in such a manner as to not be conducive to being joined. They:

1. have a fixed estimated row count that could throw the optimizer off (T-SQL multi-statement TVF = 1 row, SQLCLR TVF = 1000 rows), and
2. do not have any statistics on their columns.

For these two reasons, it is often best to dump the results to a local temporary table and then JOIN to that. So, you could accomplish this easily enough by doing the following:

1. Create a stored procedure that:
   1. cycles through your list of servers and databases
   2. constructs a dynamic SQL string in your desired form of 'SELECT * FROM ' + sSRV + '.' + @sDB + '.MyTable' (of course, including a schema name before the table name ;-)
   3. executes that dynamic SQL
2. in the main stored procedure:
   1. create a local temporary table to hold the results from the new stored procedure
   2. INSERT INTO #LocalTempTable (column_list) EXEC dbo.NewStoredProcedure;
   3. optionally add indexes to #LocalTempTable
   4. Add joins to #LocalTempTable

This will get you what you want in a way that will perform better than the initial request, while also allowing for the list of servers and databases to fluctuate over time.