How to query from a linked server via a linked server in sql server?

I have the following set-up:

SQL-Server 1 --linked--> SQL-Server 2 --linked-> SQL-Server 3


The question is can I query SQL Server 3 from SQL Server 1 via SQL Server 2?

I have unfortunately several restrictions:

• cannot use openquery

• cannot use views on Server 2 (there would be many of them and they'd have to be maintained)

• cannot create a link from Server 1 to Server 3 (due to firewalling issues)

• servers are MS SQL Servers

Unfortunately, with the conditions you've placed on your question, there is no way to accomplish what you want.

SQL Server would need to support 5 part naming such as:

SELECT *
FROM server1.server2.database.schema.table;

Which is clearly not going to work.

If you're not afraid of a little dynamic SQL, you could use something like this:

DECLARE @cmd nvarchar(max) = N'
DECLARE @cmd nvarchar(max) = N''
SELECT @@SERVERNAME; --this would be where your query goes.
'';
EXEC (@cmd) AT linked_server_2;
';
EXEC (@cmd) AT linked_server_1;

Essentially, the above code executes a dynamic SQL string over linked_server_1, but the dynamic SQL is actually an embedded dynamic SQL string, which executes a query at linked_server_2.

StackExchange Database Administrators Q#78196, answer score: 6