Solved

SQL Server 2008 Error Linking to Oracle DB

Posted on 2011-09-02
6
347 Views
Last Modified: 2012-06-21
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.  Screen Shot of Error Message
0
Comment
Question by:Mswetsky
[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
 
LVL 6

Expert Comment

by:c1nmo
ID: 36473142
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.
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 36473358
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. Error Message
0
 
LVL 6

Expert Comment

by:c1nmo
ID: 36473491
I don't have impersonate ticked, do you?
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 1

Author Comment

by:Mswetsky
ID: 36473582
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
0
 
LVL 6

Accepted Solution

by:
c1nmo earned 500 total points
ID: 36473666

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 jsched_job, jsched_calloff, CASE WHEN jsched_calloffd < TO_DATE(''17530101'',''YYYYMMDD'') THEN TO_DATE(''99991231'',''YYYYMMDD'') ELSE jsched_calloffd END AS JSCHED_CALLOFFD FROM redlive.sfjsched')
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'
0
 
LVL 1

Author Closing Comment

by:Mswetsky
ID: 36473699
I dont know what we did to get the linked server working but after adding the remote login and removing it I found that the user account had gotten locked. After unlocking the account the linked server started to show tables. Thanks for the query hints!
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to recover a database from a user managed backup
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

734 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