Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Database Connection in SQL Server 2017 (Database Collation)

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; 

 

 

1 answer

1 vote

Hello @Cormant Systems Administrator ,

Check out the following Bug that is most likely the cause here:

The main takeaway is that Microsoft SQL Server will use the Servers instance-level collation rather than the Database level collation when creating temporary tables, and in doing so will set the incorrect collation on some of the tables, when the server and the database have mismatched collations.

to get around this the workaround is:

Workaround

Change the instance-level collation to match the Jira database collation toSQL_Latin1_General_CP437_CI_AIorLatin1_General_CI_AS. You can use this guide to do so:Set or Change the Server Collation.

Regards,
Earl

Dear Earl,

I already try this method.

ALTER DATABASE [database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 

GO 

ALTER DATABASE [database] COLLATE Latin1_General_CI_AS; 

GO 

ALTER DATABASE [database] SET MULTI_USER; 

GO 

I also perform ALTER DATABASE to SQL_Latin1_General_CI_AI but result are the same.Collation1.PNG

Hello @Cormant Systems Administrator ,

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

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

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

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 ...

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
TAGS
Community showcase
Published in Jira Service Management

JSM June Challenge #2: Share how your business teams became ITSM rockstars

For JSM June Challenge #2, share how your non-technical teams like HR, legal, marketing, finance, and beyond started using Jira Service Management! Tell us: Did they ask to start using it or...

167 views 6 7
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you