SQL: not exist with openquery not working as expected

Question

Hi,

I do have an oracle 8 database from which I want to fetch data to SQL Server 2005. The following statement works fine, if the table on SQL Server 2005 is empty. If I run with let's say one entry missing, it does not work. Please let me know, if any additional information would be usefull!

SELECT wdmsoracle.NO
FROM (Select * from OPENQUERY(msch,  'select 
                                                actu.NO
                                                , actu.ANNOSEQUENT
                                                , replace(replace(dm.descript, ''Generated by modification request '', ''Modification request''), ''Pseudo ECR for WDMS-SAP interface'', ''New Drawing'')
                                                , actu.APPRDATE
                                                , actu.MADEDATE
                                                , actu.MAINDRW
                                                , actu.DESIGNGRP
                                                , actu.ITEMID
                                                , actu.ISSUE
                                                , actu.DESCRIPT
                                                , actu.DESCRIPT2
                                                , dr.PRODUCTCODE
                                                , mpaths.webpath as asdasd
                                                , vpaths.webpath
                                            FROM 
                                                WDDRAWACTU actu
                                                , wddraw dr
                                                , wddvfilesiss vfiles
                                                , wddvpaths vpaths
                                                , wddmfiles mfiles
                                                , wddmpaths mpaths
                                                , wdanno dm
                                            WHERE actu.apprdate >= to_date(''01-01-05'',''dd-mm-yy'')
                                                and actu.itemid = dr.itemid (+)
                                                and actu.issue = dr.issue (+)
                                                and actu.annosequent = dm.sequent (+)
                                                and actu.itemid = vfiles.itemid (+)
                                                and actu.issue = vfiles.issue (+)
                                                and vfiles.pathid = vpaths.pathid
                                                and actu.annosequent = mfiles.sequent (+)
                                                and mfiles.pathid = mpaths.pathid')) as wdmsoracle
Where NOT EXISTS (
        SELECT wdmsoracle.NO
        FROM [DesignMessage_Workflow].[dbo].[WDMS] wdms, (Select * from OPENQUERY(msch,  'select 
                                                actu.NO
                                            FROM 
                                                WDDRAWACTU actu
                                            WHERE actu.apprdate >= to_date(''01-01-05'',''dd-mm-yy'')')) as wdmsoracle
        WHERE wdms.NO = wdmsoracle.NO)

Answer 1

with the help of this website I was able to solve it on my own:

SELECT wdmsoracle.NO
        FROM (Select * from OPENQUERY(msch,  'select 
                                                actu.NO
                                                , actu.ANNOSEQUENT
                                                , replace(replace(dm.descript, ''Generated by modification request '', ''Modification request''), ''Pseudo ECR for WDMS-SAP interface'', ''New Drawing'')
                                                , actu.APPRDATE
                                                , actu.MADEDATE
                                                , actu.MAINDRW
                                                , actu.DESIGNGRP
                                                , actu.ITEMID
                                                , actu.ISSUE
                                                , actu.DESCRIPT
                                                , actu.DESCRIPT2
                                                , dr.PRODUCTCODE
                                                , mpaths.webpath as asdasd
                                                , vpaths.webpath
                                            FROM 
                                                WDDRAWACTU actu
                                                , wddraw dr
                                                , wddvfilesiss vfiles
                                                , wddvpaths vpaths
                                                , wddmfiles mfiles
                                                , wddmpaths mpaths
                                                , wdanno dm
                                            WHERE actu.apprdate >= to_date(''01-01-05'',''dd-mm-yy'')
                                                and actu.itemid = dr.itemid (+)
                                                and actu.issue = dr.issue (+)
                                                and actu.annosequent = dm.sequent (+)
                                                and actu.itemid = vfiles.itemid (+)
                                                and actu.issue = vfiles.issue (+)
                                                and vfiles.pathid = vpaths.pathid
                                                and actu.annosequent = mfiles.sequent (+)
                                                and mfiles.pathid = mpaths.pathid')) as wdmsoracle
Where NOT EXISTS (
        SELECT wdms.NO
        FROM [DesignMessage_Workflow].[dbo].[WDMS] wdms
        WHERE wdms.NO = wdmsoracle.NO)