Hi All,

I am having a problem I have not encountered before. I am working with a new client database in SQL Server 2000, SP4. When I run the following query, zero results are returned:

SELECT * FROM pcalot 
WHERE clotpk NOT IN (SELECT cbcnlotfk FROM pcabcn) 

However, if I perform one of the following, I get the expected result set returned:

SELECT * FROM pcalot 
WHERE clotpk NOT IN (SELECT cbcnlotfk FROM pcabcn WHERE cbcnlotfk IS NOT NULL) 

OR 

SET ANSI_NULLS OFF 
SELECT * FROM pcalot 
WHERE clotpk NOT IN (SELECT cbcnlotfk FROM pcabcn) 

Something else, I have the same database on another machine running SQL Server 2005 and the first query statement:

SELECT * FROM pcalot 
WHERE clotpk NOT IN (SELECT cbcnlotfk FROM pcabcn) 

returned the expected results no matter what the setting of SET ANSI_NULLS.

Does anybody know why SQL Server 2000 is behaving this way. I haven't seen this before until working with this specific database. Is there some type of setting I am overlooking that is different between the database in SQL Server 2000 and SQL Server 2005 or did I get a brain cramp and this has been the behavior in SQL Server 2000 all along?

Any help would be greatly appreciated.

Thanks in advance,

James