CODEWITHSUNDEEP
sqlsql-servert-sqlssms
Welsh King
Welsh King

Reputation: 3238

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

Upvotes: 1

Views: 361

Answers (1)

marc_s
marc_s

Reputation: 755451

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!

Upvotes: 3

Related Questions