SQL Error

[lester Kacprzak]

Cannot resolve the collation conflict between "SQL_Latin1_General_CP437_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

SELECT d.directory_name AS "Directory",

    u.user_name AS "Username",

    from_unixtime((cast(attribute_value AS UNSIGNED)/1000)) AS "Last Login"

FROM cwd_user u

JOIN (

    SELECT DISTINCT child_name

    FROM cwd_membership m

    JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID

    ) AS m ON m.child_name = u.user_name

JOIN (

    SELECT *

    FROM cwd_user_attributes

    WHERE attribute_name = 'login.lastLoginMillis'

    ) AS a ON a.user_id = u.id

JOIN cwd_directory d ON u.directory_id = d.id

ORDER BY "Last Login" DESC;

[mogavenasan]

Hi @lester Kacprzak,

It looks like you have a mismatch of two different collations in your table. You can check what collations each column in your table(s) has by using this query:

SELECT object_name(object_id) as TableName, name as ColumnName, collation_name
FROM sys.columns
WHERE collation_name != 'SQL_Latin1_General_CP437_CI_AI' AND collation_name != 'Latin1_General_CI_AI'
AND object_name(object_id) NOT LIKE 'sys%'
AND object_name(object_id) NOT LIKE 'queue%'
AND object_name(object_id) NOT LIKE 'file%'
AND object_name(object_id) NOT LIKE 'spt%'
AND object_name(object_id) NOT LIKE 'MSrep%'
AND object_name(object_id) NOT LIKE 'sqlagent%'
AND object_name(object_id) NOT LIKE 'plan_persist%'

If there's a mismatch, you will need to fix the collation as per How to fix the collation of a SQL Server database for Jira manually. 

I hope this helps.

Thanks,
Moga