Solved

SQL Server 2008 Error Linking to Oracle DB

Posted on 2011-09-02
6
340 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
add more rows to hierarchy 3 25
SQL Log size 3 17
TSQL XML Namespaces 7 23
Run Stored Procedure uisng ADO 5 20
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

860 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