SQL Server collate syntax error

I have searched using collate and am using it in the same way the answers say to use it; however, I am getting an error

Incorrect syntax near 'COLLATE'

Here is my query:

SELECT P.FIRST_NAME_SRCH, P.LAST_NAME_SRCH, ' ' as Title, CASE E.FULL_PART_TIME WHEN 'F' THEN 'Full Time' WHEN 'P' THEN 'Part Time' WHEN 'O' THEN 'Occasional' ELSE E.FULL_PART_TIME END AS FULL_PART_TIME, ' ' as Capacity, REPLACE(E.HOME_PHONE,'/','-') as HOMEPHONE, ' ' as MobilePh, ' ' as Email, CONVERT(char(10),E.BIRTHDATE,101) as 'BIRTHDATE', CASE WHEN E.HIRE_DT > E.REHIRE_DT THEN CONVERT(char(10),E.HIRE_DT,101) WHEN E.REHIRE_DT > E.HIRE_DT THEN CONVERT(char(10),E.REHIRE_DT,101) ELSE CONVERT(char(10),E.HIRE_DT,101) END as 'HIRE_DT', ' ' as CommPref, RTRIM(K.LEVEL3) as 'JOBCODE', E.EMPLID FROM HRPROD..PS_EMPLOYEES E, HRPROD..PS_PERSONAL_DATA P, TKCSDB..CTRLEVEL3CFG K WHERE E.COMPANY = 'WSQ' AND E.EMPLID = P.EMPLID AND K.VAL106 COLLATE DATABASE_DEFAULT = E.JOBCODE COLLATE DATABASE_DEFAULT ORDER BY P.LAST_NAME_SRCH 

I have tried (with same syntax error)

AND UPPER(K.VAL106) COLLATE DATABASE_DEFAULT = UPPER(E.JOBCODE) COLLATE DATABASE_DEFAULT 

and (returns Cannot resolve collation conflict for equal to operation.)

UPPER(K.VAL106) = UPPER(E.JOBCODE) 

What am I doing wrong in the syntax?

1 Answer

This must be a version thing. Executing via the SQL SERVER 2012 Mgt Studio query tool returns the expected results without error.