Jacco and Diane,
In SQL 7, the column type always took precedence in a
comparison between a column and a value/variable/expression.
In SQL 2000, the comparison is made using the higher-precedence
data type, without regard to where the values come from. This
is a more sensible thing to do, but as you see, it can cause problems.
In fact, it can cause major changes to efficiency, if an index can no
longer be used because of a type conversion.
The only thing that might work in terms of a setting is to put the
database into 70 compatibility mode, but you will lose other benefits
of 2000. Better to rewrite the queries so that any expressions are of
the same type as the columns they are being compared against, using
CAST or CONVERT as necessary.
-- Steve Kass
-- Drew University
-- Ref: 165CF8C2-4ABC-42EE-826C-EF94B718F92E
Jacco Schalkwijk wrote: