How to employ the "USE [mydb]" command with remote linked servers

Question

I have a suite of SSRS reports, that use dynamic SQL and employ the "USE" command.

For example, once the Data Source has been established, I do this:

  SET @wsql = 'USE mydb SELECT * FROM mytable'  

Is it possible to modify the connection string so that I can pull data from a linked server?

Here is some pseudocode to explain what I'm looking for:

SET @wsql = 'USE linkedserver1.mydb SELECT * FROM mytable'  

I know I can always change the T-SQL and do this:

SET @wsql = 'SELECT * FROM linkedserver1.mydb.dbo.mytable'  

but most reports use the "USE" command.

Answer 1

No, you cannot do USE on a remote database.

But rather than using USE, you can do:

   SELECT @sp_executesql = @mydb.sys.sp_executesql  
   EXEC @sp_executesql N'SELECT * FROM mytable'  

This works, because:

1. You can give a variable name to EXEC for which stored procedure to use.
2. sp_executesql will execute in the context of the database from which it was invoked. Which will be the database in @mydb in this case.

And you can also do:

   SELECT @sp_executesql = @linkedserver.mydb.sys.sp_executesql  
   EXEC @sp_executesql N'SELECT * FROM mytable'  

One obvious restriction: the remote data source must be another SQL Server instance.

Answer 2

Hi @Coreysan ,

Try something like :

SELECT * from openquery(LINKED_SERVER_NAME,'SELECT * FROM TABLE')   

If your linked server is well configured, you should be able to query the tables in the linked server using openquery.

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

configure the appropropriate "Data source" for your report so you dont need to use linked server
rather than using "select from linkedserver1.mydb"
why not add linkedserver1 as a data source for your report?
and all you need to do is query the table.
remeber that a report can have multiple data sources