Manage staff page has (SQL collation) errors

Question

When opening the Manage staff page I got many errors in the console log pointing to SQL collation problem.

Answer

As stated by Atlassian (https://confluence.atlassian.com/adminjiraserver083/connecting-jira-applications-to-sql-server-2017-976767468.html), the only supported collations are SQL_Latin1_General_CP437_CI_AI and Latin1_General_CI_AI as case-insensitive, accent-insensitive, and language neutral collation types. This means that the default collation of the database and the collation of all database tables and all columns have to be the collation supported by Jira.

Run these two SELECT queries in your database:

^{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%'}

^and

^{SELECT object_name(object_id) as TableName, name as ColumnName, collation_name
FROM sys.columns}

The result from the first entry should be empty and the result from the second query should have the same collation for all columns in all tables. If this is not the case, the collation has to be changed.

Follow Atlassian's instructions as described here: https://confluence.atlassian.com/jirakb/how-to-fix-the-collation-of-a-sql-server-database-for-jira-776646810.html

Please note: This is caused by SQL collation not supported by Atlassian.