The other day I was converting an existing sql query from 2000 to 2008.  This query is based off of a table that I import every night.  I created this table using the SQL Import query and it automatically determined the field data types from a Progress 10.1 database.  The only problem is when i tried to run a query on the new 2008 server I got the following error:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

After doing some research, I found out that this occurred because sql made a field in one of my tables into a Text instead of a Varchar.  For whatever reason, on the import, it translated it differently.  Also, I only had this problem with this field when it was contained in a query with a “Group By” clause.  I could probably fix this on the import, but a quick and dirty fix was to convert it to a varchar on the fly.  Here’s an example  Let’s say my field is “[product-desc]”

SELECT     TOP (100) PERCENT CONVERT(varchar(200), [product-desc])
FROM         PUB.[my-table]
GROUP BY CONVERT(varchar(200), [product-desc])

That was all I needed to do to fix this problem.  You could also fix this by using CAST like so

 

SELECT     TOP (100) PERCENT  [product-desc]
FROM         PUB.[my-table]
GROUP BY cast([product-desc] as varchar(500))

Advertisements