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:


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

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

  • Does it not work if you use the fully qualified name (linked server), e.g. select * from server.database.schema.table? Also try this: mssqltips.com/sqlservertip/1757/…
    – gareth
    Dec 14, 2016 at 10:30
  • This is the problem - I need fully qualified name as parameter. Dec 14, 2016 at 10:44
  • Use synonyms in your main db and send a db name parameter to your function. And depending on the value of the parameter use a CASE/IF to get data from the corresponding client db.
    – Marian
    Dec 14, 2016 at 10:45
  • I thought about this @Marian, but there are 2 problems: 1st the count of client DBs can increase to several hundreds and more and I also need to change the code of the engine each time a new DB is introduced :( Dec 14, 2016 at 10:48

3 Answers 3


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!

  • 10x, @DavidSpillett. I have same way of thoughts, but I hoped that I'd missed something. Dec 14, 2016 at 12:57

I think this post answers the question well. Using



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


  • I need to make JOIN with result of the select, can you add code to show me how to do that? Dec 14, 2016 at 11:03

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.