1

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.

  • 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 '16 at 10:30
  • This is the problem - I need fully qualified name as parameter. – Bogdan Bogdanov Dec 14 '16 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 '16 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 :( – Bogdan Bogdanov Dec 14 '16 at 10:48
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!

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

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,

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

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, privacy policy and cookie policy

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