Welsh King Welsh King - 5 months ago 16
SQL Question

Varchar(max) showing as text in SQL Server Management Studio

I have two columns in my database that is running on SQL Server 2008R2(10.50.4000) that are varchar(max), but in SQL Server Management Studio (11.0.5343.0) they show as text in the Explorer window.

enter image description here

When I run a query

SELECT DISTINCT
J.TICKETID,
J.jobNotes,
J.jobScopeOfWork
FROM
sysdba.ALX_JOB J
JOIN
sysdba.ALX_JOBTYPE T ON J.JOBTYPEID = T.ALX_JOBTYPEID
JOIN
sysdba.ALX_JOBSTATUS S ON J.STATUSID = S.ID
JOIN
sysdba.ADDRESS A ON J.ADDRESSID = A.ADDRESSID
LEFT OUTER JOIN
sysdba.ALX_JOB_JOBUSERS ju ON j.TICKETID = ju.ticketId
WHERE
(ju.ALX_USERID = '12345' OR j.UserID = 12345)


I get the error


Msg 421, Level 16, State 1, Line 1

The text data type cannot be selected as DISTINCT because it is not comparable.

Msg 421, Level 16, State 1, Line 1

The text data type cannot be selected as DISTINCT because it is not comparable.


The compatibility option is set as follows

enter image description here

Answer

If your database engine is running on SQL Server 2000, or if your database compatibility level is set to "80" (SQL Server 2000), then that database doesn't know about varchar(max) yet.

Change your compatibility level to a more recent version!