not in and sql server version

[timc]

I have a client DB running sql server 8.00.760 which has a query which will not run correctly ( it has a sql statement which last_name not in (select p.last_name from table p).

I copied the db back here and the same statement runs fine. We are running 8.00.977
Has anyone seen this before?

[Rawhide]

You have to give us a little more information than that. What do you mean by "will not run correctly"?

Does it give different results than you expect? Does it give you an error? Or what?

What is the actual query? So many time the "sample" query people post on here is different than the query they are actually running and as a result, the help we provide doesn't apply.

Check the client db to see if it is set to be case-sesitive by using upper() or lower(). If more records are returned, then it is case-sensitive.

Where Upper(last_name) not in (select Upper(p.last_name) from table p)

[timc]

What we are seeing was no records returned, when there should have been. We discovered there were some null values in this field. We removed those and it worked. To me it still seems it should have worked anyhow

[rmiao]

Did you compare 'ANSI null default' setting between you and your client?

[timc]

no, I did not, should I do this at db level or tsql

[rmiao]

Don't think in db level since you got copy from your client. You can check server connection settings in properties page in em, and connection properties in query analyzer's option setup.