SAME QUERY BRINGS DIFFERENT RESULT BETWEEN MSDAORA AND ORACLE PROVIDER OLE DB

  • ggramm

    Valued Member

    Points: 52

    Dear All,

    I have one view in oracle which gives no result, and it is correct!!!

    --------------------------------------------------------------------------------------

    SELECT DOCID, CUS.tracode,LEE.LEENAME,SDT.dotcode,SLD.docnumber,SLD.docenimerosisdate
    FROM SLD,
          CUS,       LEE,       SDT
    WHERE CUS.traid=SLD.traid
    AND   LEE.leeid=CUS.leeid
    AND   SLD.dotid=SDT.dotid AND   SDT.dotcode in ('ΕΤΔ','ΗΕΤΙΙ','ΕΤΙΙ', 'ΕΠΚ', 'ΗΕΠΚ')

    AND   SLD.docenimerosisdate >='02/02/2018'
    AND TRACODE IN ('09751','14698','05074','05044','05182','15656','05097')
    AND SLD.pmtidpayment NOT IN ( 133,119)

    -----------------------------------------------------------------------------------------------------------------------

    when I run this from Oracle it is ok.

    when I run this from SQL Server 2008 ( win2k3 server ) MSDAORA result is OK

    when I run this from SQL Server 2008 ( win2k8 server ) Oralce Ole DB result is NOT OK.

    I realize that the problem is the date format . But I can not change the date format  in oracle because it wont run from Oracle tool

    Is there any idea how to solve the problem

    Thanks in advancefor your help

  • Ken McKelvey

    SSCoach

    Points: 18224

    Try specifying the date in ISO format:
    AND SLD.docenimerosisdate >='20180202'

  • ggramm

    Valued Member

    Points: 52

    I tried yours and
    when I run this from SQL Server 2008 ( win2k3 server ) MSDAORA

    result is
    Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "SENDB".

    when I run this from SQL Server 2008 ( win2k8 server ) Oralce Ole DB result is OK.!!!!!

  • Sue_H

    SSC Guru

    Points: 89891

    ggramm - Wednesday, July 25, 2018 4:56 AM

    I tried yours and
    when I run this from SQL Server 2008 ( win2k3 server ) MSDAORA

    result is
    Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "SENDB".

    when I run this from SQL Server 2008 ( win2k8 server ) Oralce Ole DB result is OK.!!!!!

    Check the following article to see what the expected format is and the related connection properties. Refer to the Date Formats section -
    Features of OraOLEDB

    Sue

  • Jo Pattyn

    SSC-Dedicated

    Points: 31258

    or you could use the oracle to_date function

    AND SLD.docenimerosisdate >='02/02/2018'
    AND SLD.docenimerosisdate >=TO_DATE('02/02/2018','DD/MM/YYYY')

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply