Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

SQL Server 2008 Error Linking to Oracle DB

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
Mswetsky
Asked:
Mswetsky
  • 3
  • 3
1 Solution
 
c1nmoCommented:
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
 
MswetskyAuthor Commented:
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
 
c1nmoCommented:
I don't have impersonate ticked, do you?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
MswetskyAuthor Commented:
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
 
c1nmoCommented:

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
 
MswetskyAuthor Commented:
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

Independent Software Vendors: We Want Your Opinion

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!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now