sql server linked servers

How can I access the linked server in one sql server instance from another instance?  I have a sql server called DEV1.  I have another sql server called DEV2.  DEV2 is a linked server to DEV1.  I access from DEV1 by:
"select * from DEV2.<dbname>.<schema>.<table>".  
DEV2 has a linked server DEV3.  How can I access DEV3 from DEV1?
dhenderson12Asked:
Who is Participating?
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

>> so I can't connect to a linked server on another linked server?
Correct.

They don't cascade. That is, server1.server2.server3.database.dbo.tablename doesn't work

But what you can do is
select * from openquery( server1, 'select * from openquery( server2, ''select * from something'' )' )

Do note that I may not have gotten the doubling up on quotes correct, but you get the idea.

Regards
  David
0
 
John_VidmarCommented:
When using a 4-part naming convention, server.database.schema.object, server must be the one you are on or it must be configured as a linked-server.  Otherwise, you should get the following error:
Msg 7202, Level 11, State 2, Line 4
Could not find server 'DEV3' 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.

Open in new window

Use sp_addlinkedserver to configure DEV3 as a linked-server on DEV1, I'm assuming there is no firewall preventing communication.
0
 
dhenderson12Author Commented:
so I can't connect to a linked server on another linked server?
0
 
John_VidmarConnect With a Mentor Commented:
The phrase linked-server is confusing... SQL Server allows you to communicate with other SQL Servers, other RDBMS (IBM, Oracle, Sybase), and other technologies (spreadsheets, web-services, access, tonnes of stuff).  A linked-server is just a configuration that tells your SQL Server how to communicate with something that resides outside of your server (whether its the same technology or something totally different).

In your case, you have 3 physical servers (DEV1, DEV2, DEV3), and you want to be able to go to any of those servers and get data from the other servers... that's when you need the configuration called linked-server.  I would:
go on DC1 and create linked-servers to DC2 and DC3
go on DC2 and create linked-servers to DC1 and DC3
go on DC3 and create linked-servers to DC1 and DC2

With that in place, you can be connected to any server and access data from the other servers.
0
 
dhenderson12Author Commented:
Thanks for the guidance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.