-1

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
  • 1
    Here we have a pretty good example of a question that I can claim it is legit since it got more than 2500 views meaning that many people came across this page most probably because they had a similar question in mind, but yet it is voted down because there are impatient users out there who instead of trying to help improve a question with constructive feedback they just rush to push on the down vote button. We need a system to identify this behaviour and try to revive such voted down questions. – kaptan May 1 '18 at 18:56
  • Downvoters should leave a comment as to why. I want to remove a linked server and needed to check downstream dependencies, this question answers one aspect of that - whether those downstream dependencies could be coming in via a linked server themselves. Have upvoted to counter 1 of the downvotes – Will Apr 12 '19 at 17:36
  • tnx @Will. One up-vote at a time : ) – kaptan Apr 12 '19 at 21:17
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.

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

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.