We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
8 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Server 2008 Error Linking to Oracle DB
Posted on 2011-09-02
I am trying to link to an Oracle database with SQL Server 2008 R2. I have installed the Oracle 11G client and the TNSnames.ora file. I have also set up the DSN and tested the connection to the Oracle DB and it reported success. Where I run into an error is when I try to open a Oracle table from within SQL Server Management Studio in the Linked Servers Section. I get an error message saying "Failed to retrieve data for this request...." I have included a screen shot of the error message. I have never linked to an Oracle DB before so any help would be greatly appreciated. 
[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
-
3
-
3
6 Comments
What do you have under security in your linked server properties? I have this running ok with a local login mapped to the oracle (remote user). I've got 'Be made using this security context:' selected and again have the oracle username and password in there.
I mapped the local logon to the remote user account and also have the "be made using this context.." selected and still received errors. I will include a screen shot of the error. 
I have gotten this to work now to the point where I can open up a list of the tables within the Oracle DB. My problem now is that I am unable query them. What is the proper syntax for running a query on this linked server ex. name : PDW2.WHISC.BCTBL
You can use openquery or direct, examples of each against a linked server called redthorn below:
--select jobs from schedule falling within range
SELECT * into #FullSchedList FROM OPENQUERY(redthorn,'SELECT
where jsched_calloffd >= @StartPeriod and jsched_calloffd <= @EndPeriod
--filter out J_REF_STATUS 3 and 9
SELECT #FullSchedList.* into #SchedListStatus FROM #FullSchedList
INNER JOIN redthorn..REDLIVE.SFJ ON jsched_job = J_REF_JOB AND J_REF_STATUS <> '3' and J_REF_STATUS <> '9'
Featured Post
What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 4 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.