I have a client I was building an e-commerce website for. I was developing the website on MS SQL 2005 Database, but their deployment server have MS SQL 2000. From experience I knew there are some stuffs that would not work in MS SQL 2000, so I avoid them. This one missed my eyes. I get this error message on the search results page:

The text, ntext, and image data types are invalid in this subquery or aggregate expression.

After checking I found I was trying to get a TEXT column in a subquery, and that was causing the problem. An example query is:

Select customerName,customerNote=(select top 1 note from customerNotes Where customerId=C.customerId) from Customers C

Note that the column NOTE is a text column that was causing the problem.

Solution:

The text column need to be retrieved as a varchar column. Rewriting the query this way solves the problemma:

Select customerName,customerNote=(select top 1 Cast(note As varchar(500)) from customerNotes Where customerId=C.customerId) from Customers C

You can also use Convert instead of Cast like this:

Select customerName,customerNote=(select top 1 Convert(varchar(500),note) from customerNotes Where customerId=C.customerId) from Customers C