Openquery returns few rows

  • 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,

  • 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?

  • Maybe it is related to oracle oledb fetchsize (default 100) OraOLEDB-Specific Connection String Attributes for Rowsets

  • 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.

  • Its is working fine now.. Thanks for all your inputs.

  • Glad it works now, what have you done to make it work?

  • 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