Database Connection in SQL Server 2017 (Database Collation)

Question

JIRA Old Version 7.11.2 - SQL Server 2012 Upgrade JIRA version to 8.8.1 SQL Server 2017 Database Collation. The table collation: 'Latin1_General_CI_AS' is unsupported by Jira. The Database collation: 'Latin1_General_CI_AI' is supported by Jira We do some action base on the solution below. How to Verify Table Collations SELECT DISTINCT C.collation_name FROM sys.tables AS T INNER JOIN sys.columns C ON T.object_id = C.object_id WHERE collation_name IS NOT NULL; How to Verify Database Collations SELECT DISTINCT C.collation_name FROM sys.tables AS T INNER JOIN sys.columns C ON T.object_id = C.object_id WHERE collation_name IS NOT NULL;

Earl McCutcheon · Answer

Hello @Serge Mbitom ,

It's a limitation in how MSSQL is implemented via the reference objects via the transitional SQL being designed for interaction with other Microsoft products but disregards applications designed around multi platform support (i.e. Jira is designed to also work on PSQL and MySQL).  If the specified collation or the collation used by the referenced object uses a code page that is not supported.  and in some cases that can be an entirely incorrect/bad way to do things for compatibility for multiplatform apps, so Jira is working around this MSSQL limitation using the most common collation standard.

The requirement is that either:

* the collation is the same for string columns being joined, or used in concatenation / UNION, etc, or
* you specify the COLLATE clause in the predicate or expression to override one or more of the columns.

So, if you know that your column has a collation of Z and your database has a default collation of Y, then create the temp table specifying COLLATE Z for that column. Then table variables use the database's default collation, not the instance's collation. And, contained databases are yet another set of rules.

Hope this helps clarify thing a bit more.

Regards,
Earl

Serge Mbitom · Answer

Why can't JIRA specify the collation when creating the table ?

Change the collation at the instance level is simply not an option. Not everyone has the JIRA database living alone on a dedicated server ...

Earl McCutcheon · Answer

Hello @Cormant Systems Administrator ,

The commands you posted are for altering the Jira database level collation as covered in:

* https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=sql-server-ver15

The workaround is noting changing the Instance level Collation via the steps in:

* Set or Change the Server Collation.

Changing the Server Collation

Changing the default collation for an instance of SQL Server can be a complex operation and involves the following steps:

* Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.

* Export all your data using a tool such as the bcp Utility. For more information, see Bulk Import and Export of Data (SQL Server).

* Drop all the user databases.

* Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:

Copy

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName   
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]   
/SQLCOLLATION=CollationName

For more information, see Rebuild System Databases.

* Create all the databases and all the objects in them.

* Import all your data.

Regards,
Earl