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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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_VidmarCommented:
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
David ToddSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dhenderson12Author Commented:
Thanks for the guidance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.