Post reply

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

  • July 25, 2018 at 3:13 am

    #373805

    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

  • July 25, 2018 at 4:45 am

    #1998918

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

  • July 25, 2018 at 4:56 am

    #1998920

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

  • July 25, 2018 at 7:56 am

    #1998942

    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

  • July 27, 2018 at 3:45 am

    #1999210

    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 4 (of 4 total)

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