Solved

sql server linked servers

Posted on 2014-02-27
5
268 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 250 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 250 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 56
Disable TLS1.0 on Win 2012 server 7 117
Tracking Problematic Page Splits 1 50
Http Post send by Store Procedure Help 5 32
In this article I will describe the Detach & Attach 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

759 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