SQL Server 2000 and SQL Server 2008 queries

We are testing the a procedure in 2008 sql server. This procedure have only following delete query.

delete from dbo.tgrid_detl 
where SOURCE_KEY in (
    SELECT tg.SOURCE_KEY
    FROM dbo.tgrid_detl tg 
    WHERE NOT EXISTS 
        (select *
            FROM dbo.tgrid_stagging s,dbo.tgrid_detl tg 
            where cast(s.SUBMISSION_NO as varchar(18)) + 
                cast(year(s.POLICY_EFCTV_DT)as varchar) + 开发者_如何学JAVA
                s.PRODCT_COVG_TYP_CD + 
                s.UW_SYSTEM_ID + 
                s.PRODUCT_ABBR + 
                s.PRODUCT_CD = tg.source_key
        )
    and tg.F_TRANS_CD = 'N'
    and tg.UPDATE_ID is null
    and tg.COMMENTS_UPDATE_DT is null 
    and tg.SOURCE_ID = 'EDW_SUB'
)

This is working fine in SQL 2000 server but it is not deleting row in sql 2008 server. Could you pleas advice on this. Thanks.

There's no immediate reason why the should behave differently. Try running this query on both to compare the result sets:

SELECT *
FROM dbo.tgrid_detl
WHERE SOURCE_KEY in (
    SELECT tg.SOURCE_KEY
    FROM dbo.tgrid_detl tg 
    WHERE NOT EXISTS 
        (select *
            FROM dbo.tgrid_stagging s,dbo.tgrid_detl tg 
            where cast(s.SUBMISSION_NO as varchar(18)) + 
                cast(year(s.POLICY_EFCTV_DT)as varchar) + 
                s.PRODCT_COVG_TYP_CD + 
                s.UW_SYSTEM_ID + 
                s.PRODUCT_ABBR + 
                s.PRODUCT_CD = tg.source_key
        )
    and tg.F_TRANS_CD = 'N'
    and tg.UPDATE_ID is null
    and tg.COMMENTS_UPDATE_DT is null 
    and tg.SOURCE_ID = 'EDW_SUB'
);

If the results are the same then you have some other configuration issue on your SQL 2K8 server. Access rights to delete from the table is the first thing that comes to mind.