Linked server is invisible in SQL server
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.
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.
ASKER
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.
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.
have you used sp_addlinkedserver at all?
ASKER
I created this particular instance of linked server via GUI, exactly as the link you've provided suggests.
What do you get from EXEC sp_linkedservers ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I didn't know different panes in SQL Management Studio can be connected to different servers.
if this does not help, could you provide the complete (unabridged) error message?
(i'm assuming there was a bit more)