Document toolboxDocument toolbox

To learn more about Tempo products, please visit our Help Center. For support, see our Support Portal.

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.