Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Server 2008 Error Linking to Oracle DB

Posted on 2011-09-02
6
Medium Priority
?
370 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:Mitch Swetsky
  • 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:Mitch Swetsky
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
LVL 1

Author Comment

by:Mitch Swetsky
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 2000 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:Mitch Swetsky
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to recover a database from a user managed backup
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

606 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