Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql server linked servers

Posted on 2014-02-27
5
Medium Priority
?
278 Views
Last Modified: 2014-02-28
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?
0
Comment
Question by:dhenderson12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39891820
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
 

Author Comment

by:dhenderson12
ID: 39892044
so I can't connect to a linked server on another linked server?
0
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 1000 total points
ID: 39892180
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
 
LVL 35

Accepted Solution

by:
David Todd earned 1000 total points
ID: 39894189
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
 

Author Closing Comment

by:dhenderson12
ID: 39895891
Thanks for the guidance.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question