4

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

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.

0
4

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

3

Add a view on server 2, and query the view from server 3

1
  • 1
    Yes, this works. But I was looking for a solution that can directly query without creating a view or anything like that in the middle sql server. – kaptan Oct 2 '14 at 0:18
2

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:

  1. The data in both directions must flow along the chain of servers. So if you select 50MB of data in the above example, you will transfer 50MB of data from server3 to server2, then again from server2 to server1. So you're doubling your network traffic.
  2. Therefore, this will obviously be slower.
  3. You're also giving all three servers more work to do.
  4. The login used by the linked server must have execute access on the target side.
  5. Your DBAs may throw things at you.
  6. Your network security people may interpret this as an attempt to bypass their security, and they'll throw things at you.

Seriously, just put in a firewall exception request.

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.