Solved

sql server linked servers

Posted on 2014-02-27
5
262 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

862 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now