Hi All,

I have been using SQL Server 2000 for some time now and decided to change to SQL Server 2005 Express.

I have a SQL statement as follows

SELECT CAST(ID AS VARCHAR(50)) AS ID, CAST(FILE_ID AS VARCHAR(50)) AS FILE_ID FROM tblTest ORDER BY CAST(ID AS INT)

The reason for this statement is I want to be able to search the recordset using ADO in VB6 using FIND
rs.Find ID LIKE '1%'

So the SELECT CASE converts it to a VARCHAR and the ORDER BY CAST keeps the numbers in a numerical order.

It works fine using SQL Server 2000 but SQL Server 2005 it gives me a "The comparison operator was invalid" error message but only on the ID column. If I modify the FIND statement to rs.Find FILE_ID LIKE '1%' I do not get this error and it all works as expected.

I looked at the datatypes in the table and they were the same, the only difference was the ID column is set to a PRIMARY KEY. I removed the primary key and the problem went away.

Has anyone encountered this issue and do you have any workarounds. Is this a bug with SQL 2005 or ADO??

Any help is appreciated.