Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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:
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.
Add a view on server 2, and query the view from server 3
use the Execute at syntax.
-- from server1, talking to server2
select top 5 * from server2.db.dbo.tbl
-- from server1, talking to server2
execute ('select top 5 * from tbl') at server2
-- from server1, telling server2 to talk to server3
execute ('select top 5 * from server3.db.dbo.tbl') at server2
And, assuming this works, you can continue daisy-chaining SQL servers in this manner.
However, be aware:
Seriously, just put in a firewall exception request.
The best thing to do is to add a Linked server from Server 1 to server 3 and use a FOUR part naming convention.
select column_name from [linkedserver3].[databasename].[schemaName].[object_name]
For Linked Server - Query optimizer creates an execution plan by looking at the query nomenclature and breaks it into remote and local queries. Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to end user as single record set.
For OPENQUERY - Executes the specified pass-through query on the specified linked server. SQL Server sends pass-through queries as un-interpreted query strings to an OLE DB data source . Hence, SQL won’t apply any kind of logic on the query and won’t try to estimate what that query would do, it would simply pass the specified query as it is to the target linked server. Open queries are useful when you are not referencing multiple servers in one query. It’s generally fast as SQL does not break it into multiple operations and does not perform any local action on the output received.
As a side note, refer to
