CODEWITHSUNDEEP
sql-serverselectlinked-server
Unik Mandakh
Unik Mandakh

Reputation: 21

SQL Server : double hop linked server running SELECT Query

I'm quite new to SQL Server. I have a question: I have 3 SQL servers, 2 on domain Domain1 and 1 on Domain2.

My Windows authentication is Admin on all Windows servers and SQL Servers across all 3 servers. I'm trying to run a SELECT query on SQLServer_1 to retrieve data from SQLServer_3 using SQLServer_2 as a middle jump point (connecting directly between SQLServer_1 and SQLServer_3 unfortunately is out of options)

I've tried running these query on SQLServer_1 :

 SELECT * 
 FROM [SQLServer_2].[SQLServer_3].[DBName_1].[Table_Name1]

But I get this error:

The object name 'SQLServer_2.SQLServer_3.DBName_1.Table_Name1' contains more than the maximum number of prefixes. The maximum is 3.

 SELECT * 
 FROM [SQLServer_3].[DBName_1].[Table_Name1]

results in:

Could not find server 'SQLServer_3' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

So both didn't work.

Please HELP. Is it even possible?

Upvotes: 1

Views: 1196

Answers (1)

Unik Mandakh
Unik Mandakh

Reputation: 21

Thank you ZLK 23. He's suggestion worked. I dont know how to post it as an answer if any one does please help me out.

Perhaps use OPENQUERY on the second server to run a query on the third? e.g. on server 1 select * from openquery(server2, 'select * from server3.database.schema.table;'); Alternatively, you might consider executing a stored procedure on server2 (that connects to server3) from server1. – ZLK 23 hours ago

I ran this query from SQLServer_1. In my understanding it runs query SELECT on SQLServer_2 the SELECT QUERY in the second parameter and retrieves the results back. Which has worked perfect for me.

SELECT * FROM OPENQUERY(SQLSERVER_2,'SELECT * FROM 'SQLSERVER_3.DBNAME.TABLENAME')

Thank you again.

Upvotes: 1

Related Questions