Solved

Linked server is invisible in SQL server

Posted on 2013-06-24
7
495 Views
Last Modified: 2013-06-30
Hello,

We work with SQL Server 2008 R2. I am attaching a screenshot with the [Microsoft SQL Server Management Studio] window to illustrate the problem.
I have created a linked server via ODBC DSN to Oracle database. The name of this linked server is ORACLE_DB, and you can see it in the list of linked servers in the left pane.
Connection test fine, and I can see all the tables in Oracle database if I expand that node.
When I execute the sp_helpserver, though, ORACLE_DB is not listed, as you can see in the Results pane. Another linked server I have (PRDDX5600) is visible both in treeview and in the results of execution of sp_helpserver.
When I try to execute a query using the ORACLE_DB linked server, I get an error "Could not find server 'ORACLE_DB' in sys.servers."
I hope I am missing something simple. I'd appreciate if anybody could push me in the right direction.
Management Studio window
0
Comment
Question by:nebfis
  • 4
  • 2
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39273773
could you review this page, I believe it is relevant.

if this does not help, could you provide the complete (unabridged) error message?
(i'm assuming there was a bit more)
0
 

Author Comment

by:nebfis
ID: 39274579
Thank you for the link. I checked it, but it describes how to create linked server via GUI. That's exactly what I did. As I said in my original post, I even (of course) validated the connection, and I can see the list of tables on the linked server. And in the snapshot you can see the linked server in the treeview. That's what puzzles me.
As for complete error message, here it is:

Msg 7202, Level 11, State 2, Line 1
Could not find server 'ORACLE_DB' 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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39274607
have you used  sp_addlinkedserver at all?
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:nebfis
ID: 39274759
I created this particular instance of linked server via GUI, exactly as the link you've provided suggests.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39274881
What do you get from EXEC sp_linkedservers ?
0
 

Accepted Solution

by:
nebfis earned 0 total points
ID: 39274920
I have found what the problem was. The short version of the story is: my panel with SQL Query was connected to a server, which was different to the server that was displayed in a treeview.
0
 

Author Closing Comment

by:nebfis
ID: 39287895
I didn't know different panes in SQL Management Studio can be connected to different servers.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
any adverse effect when modifying data type on a busy table. 4 22
Parse this column 6 34
find SQL job run average duration 24 53
SQL server client app 3 25
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

679 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