June 14, 2012 at 7:17 am
Hi all,
Env: Oracle 11R2 64 bit, sql server 2008 R2 64 bit SP1
I was able to setup the Linkedserver to Oracle server using ODBC connection and was tested successfully.
But when i run the OPENQUERY statement i am not retreiving the total rows exists in oracle table.
Query is returning only few rows out of all.
Here is the sample query i am running-
select * from openquery ([Oracle_ServerName],
'select * from schemaname.testtable' )
Does any one faced the same issue?.
Thanks,
June 14, 2012 at 8:24 am
There are more people having the issue like psubrama2000 who found a workaround workaround at the end.
What oracle driver/what version does the linked server use?
June 14, 2012 at 8:28 am
Maybe it is related to oracle oledb fetchsize (default 100) OraOLEDB-Specific Connection String Attributes for Rowsets
June 15, 2012 at 8:27 am
Hi,
Thanks for the greatfull link.
After setup of the "OraOLEDB.Oracle" for linked server and tested the Linked server- But the no. of rows return from Openquery does not match.
Is there any other way of writing the queries to fetch the data.
I dont want to use SSIS.
Please help.
Thanks.
June 15, 2012 at 1:14 pm
Its is working fine now.. Thanks for all your inputs.
June 15, 2012 at 1:28 pm
Glad it works now, what have you done to make it work?
June 18, 2012 at 2:38 pm
Hi,
These are the steps i followed:
. Download the oracle client "win64_11gR2_client.zip" and windows driver for Oracle "ODAC112021Xcopy_x64.zip" ( both are 64 bit)
. install the win64_11gR2_client.zip with "Administrator mode".
1.Unzip the win64_11gR2_client.zip file into an appropriate location.
2.Run the setup.exe to begin the installation process.
3.Select the "Administrator" installation option. Click Next.
4.Set the Oracle Base to: C:\oracle\app
5.Set the Software Location to: C:\oracle\app\product\11.2.0\client_1
6.Click Next.
7.Wait for the installation to finish.
8.Click Close when the installation has finished.
.To install Oracle Data Access Components (ODAC) Installation:(ODAC112021Xcopy_x64.zip)
1.Unzip the ODAC112021Xcopy_x64.zip file into an appropriate location.
2.Open a Command Prompt as an "Administrator".
3.Change directory to the location of the ODAC112021Xcopy_x64 contents.
•Example: cd C:\temp\ODAC112021Xcopy_x644.Run the following command in the Administrative Command Prompt:
•install.bat all C:\oracle\app\product\11.2.0\client_1 odac
5.Wait for the command to finish processing.
6.Close the command prompt.
7.A reboot of Windows would be recommended.
. Save the TNSNAMES.ora and SQLNET.ora files in the oracle client location.
example : C:\oracle\app\product\11.2.0\client_1
. Also save the same files at location- C:\oracle\app\product\11.2.0\client_1\Network\Admin
. Add the below three paths to system environment variables:
“ORACLE_HOME=C:\oracle\app\product\11.2.0\client_1" and
add two paths to system path: “C:\oracle\app\product\11.2.0\client_1" and “C:\oracle\app\product\11.2.0\client_1\bin”.
. Now do the "TNSPING dbname" from cmd prompt to check the connection to the server.
. You can also do the UDL test to confirm the connection.
. connect to SSMS- Linked server- Providers- "OraOLEDB.ORacle" - properties- enables these below 5 options:
- Dynamic Parameters.
- Nested Queries.
- Allow in Process.
- Non Transacted updates.
- supports 'Like' Operator.
. Restart the MSDTC and sql server services.
. Create the Linked server
-- No need to change any Registry values and also No Oracle client 32bit installation is required
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply