Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Posts
    5

    Unanswered: Linked server of Sql Server to Oracle issue

    Hi all,


    I have a Linked Server object in SQL Server 2005 to query an Oracle view. The view returns all table rows.

    Linked server name in sql server 2005 is : ASE

    Linked Server object in SQL Server 2005 is connecting to Oracle view successfully.


    select * FROM OPENQUERY(ASE,'SELECT TOP 1 EMPCODEEIN FROM ASE_VIEW_00050')


    My problem while execute the above select query, I am getting below error:

    OLE DB provider "OraOLEDB.Oracle" for linked server "ASE" returned message "ORA-00923: FROM keyword not found where expected".
    Msg 7321, Level 16, State 2, Line 1
    An error occurred while preparing the query "SELECT TOP 1 EMPCODEEIN FROM ASE_VIEW_00050" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "ASE".


    Can any one help me out to resolve the issue?

    Thanks in advance.

    Thanks
    Ping

  2. #2
    Join Date
    Aug 2008
    Location
    India
    Posts
    55
    1.Do u have Oracle client tools installed in Server in which u have SQL server 2.You need to put entry in tnsnames.ora for the oracle server and try to do tnsping and also have a look at sqlnet.ora.
    3.Create a ODBC with id which is pointing to oracle linked server and test the connection

  3. #3
    Join Date
    Nov 2005
    Posts
    122
    Doesn't Oracle use LIMIT instead of TOP?

    select * FROM OPENQUERY(ASE,'SELECT EMPCODEEIN FROM ASE_VIEW_00050 LIMIT 1')

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Oracle doesn't have something similar with MSSQL TOP clause.
    You should use RANK instead:
    RANK

  5. #5
    Join Date
    Jan 2009
    Posts
    17
    select * FROM OPENQUERY(ASE,'SELECT EMPCODEEIN FROM ASE_VIEW_00050 where rownum=1)

  6. #6
    Join Date
    Feb 2009
    Posts
    5
    I found out an alternate solutions for this is below:

    SELECT top 1 EMPCODEEIN FROM ASE..ASEADMIN.ASE_VIEW_00050

    This query is working fine.

    Thanks
    Ping

  7. #7
    Join Date
    Feb 2009
    Posts
    5
    Thanks for your help!!!

    After using the above sql queries also, still I am facing the same problem

  8. #8
    Join Date
    Feb 2009
    Posts
    5
    I found an alternate solutions for this:

    SELECT top 1 EMPCODEEIN FROM ASE..ASEADMIN.ASE_VIEW_00050

    This query is working fine.


    Thanks
    Ping

  9. #9
    Join Date
    Feb 2009
    Posts
    5

    Unhappy

    I got a new problem with respect to the linked server query

    1) SELECT top 1 EMPCODE FROM ASE..ASEADMMIN.ASE_VIEW_5 where EMPCODE='304489649'

    When I run the first query EMPCODE='304489649' - It is working fine.

    But when I run the below query EMPCODE='303340810' - I am getting error "Invalid data for type "numeric".


    2) SELECT top 1 EMPCODE FROM ASE..ASEADMMIN.ASE_VIEW_5 where EMPCODE='303340810'

    Error:
    Msg 9803, Level 16, State 1, Line 1
    Invalid data for type "numeric".

    I found that whenever EMPCODE last digit is 0 (zero) - The above error is coming.

    Can any one help me out to sort this issue?

  10. #10
    Join Date
    Feb 2012
    Posts
    2

    linked server

    Its too late...

    solution for top n query
    ----------------------
    select TOP 1 * FROM OPENQUERY(ASE,'SELECT EMPCODEEIN FROM ASE_VIEW_00050')

    solution for numeric column problem
    ----------------------------------
    SELECT TOP 1 *
    FROM OPENQUERY (ASE, 'SELECT TO_CHAR(EMPCODEEIN ) AS NumberCol FROM ASE_VIEW_00050');

    OR

    SELECT TOP 1 *
    FROM OPENQUERY (ASE, 'SELECT TO_CHAR(ROUND(EMPCODEEIN) ) AS NumberCol FROM ASE_VIEW_00050');

    OR

    SELECT TOP 1 CONVERT(FLOAT, NumberCol) AS NumberCol
    FROM OPENQUERY (ASE, 'SELECT TO_CHAR(EMPCODEEIN ) AS NumberCol FROM ASE_VIEW_00050');

  11. #11
    Join Date
    Sep 2010
    Posts
    153

    Smile What is ASEadmin?

    ASE - linked server name.

    ASE_View - view in oracle i guess.

    What is ASEADMIN and Why do we use double dots(..)?

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •