Hello,
I have a job for a customer, to find out why the following query fails, and i can't really find any reason.
The customer migrated from MSDE (Sql 2000) and after that the following query fails
SELECT DISTINCT U.UNITID FROM UNIT U INNER JOIN UNITINV UI0 ON U.UNITID = UI0.UNITID INNER JOIN INV I0 ON UI0.INVID = I0.INVID INNER JOIN UNITINV UI1 ON U.UNITID = UI1.UNITID INNER JOIN INV I1 ON UI1.INVID = I1.INVID WHERE (I0.NAME = 'PC CREATOR BASED' AND I0.VALUE = 'TRUE' AND I0.Section ='SYSTEM') AND (I1.NAME = 'INSTALLDATE' AND CONVERT(Float,RTRIM(I1.VALUE)) >= 1081925657 AND I1.Section ='OPERATING SYSTEM') AND U.Type = 1 ORDER BY U.UNITID
It works like a charm on SQL 2000, but on SQL 2005 i get the following error.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
The ting i first thought caused the problem was that I1.VALUE contained not numeric data, but thats not the fault.
I think there is some kind of Join optimize thingy on SQL 2005, but i can't seem to find a reason for it.
Collation on sql 2000 was Danish_norwegian.
Collation on sql 2005 is Latin1_general_CP1_CI_AS
Now my question is.
Whats changed on 2005?
And can i set a setting so that it will work like it did on 2000?
Thanks in advance