Tag Archives: collation

K2 Five collation requirement changes

In the past I’ve wrote some blog post promoting documented K2 collation requirement. With release K2 Five this requirement changed and I guess I have to mention it on my blog as essentially it makes my old blog posts about required K2 database collation incorrect.

So recently (with release of K2 Five) all K2 documentation was updated and states that our requited collation now is “SQL_Latin1_General_CP1_CI_AS“. Where to find this information?

In K2 Installation and Configuration Guide you can find “SQL and Reporting Services Operational Requirements” section which says that:

– Case-sensitive databases are NOT supported.

– The following collation setting is required for the K2 database: SQL_Latin1_General_CP1_CI_AS

And as usually you can find the same information in K2 Product Compatibility, Integration and Support matrix:

What is good about this change is that SQL_Latin1_General_CP1_CI_AS will be default collation if you installing SQL Server on top of Windows Server which has been installed with US location/language settings – so at least some people will meet this requirement by accident.

What is bad is that collation requirement was just silently changed in documentation with release of K2 Five without any explanations. According to my current knowledge collation which was mentioned in documentation before was a requirement only for pre-4.6.11 versions of K2. So in case you are doing new installation of K2 4.6.11 or newer make sure that your SQL Server instance provisioned with SQL_Latin1_General_CP1_CI_AS collation.

Somewhat mixed blessing is change which was made to K2 Five installer to enforce this collation: what it does at the moment is just enforces this collation on K2 DB level while ignoring SQL Server instance level collation. So in case you not provisioned SQL Server instance with the right collation you will get an errors post installation and will be forced to change SQL Server instance level collation to fix this. That’s something that I hope will be corrected in K2 installer in the future so that it warns you about wrong instance level collation issues before you start your installation.

Conclusion: read vendor documentation carefully before doing your installation even if you did it many times before 🙂

Update (Feb 2018): Current guidelines are:

– For upgrade installations be sure to make SQL server instance and K2 DB collation are the same (in case you moving K2 DB onto new server)

– For clean installs I would suggest stick to Latin1_General_CP1_CI_AS (especially if you do 4.7 install). I will probably write more detailed post to explain this position later


K2 database collation requirement – finally we have it stated in the right place

If you read my old blog post on Installing SQL Server instance for K2 blackpearl you probably aware that K2 database requires very specific SQL Server instance collation in case you care to be in supported state. The main problem was that this requirement has been mentioned in quite obscure place which no sane person even reach in endless quest for knowledge 🙂 That original requirement location was quite close to that joke about ginormous EULA and vendor injecting a sentence which says: “If you really read through this EULA till this place please give us a call to claim your 1000$ reward”…

There is no doubts that K2 is very flexible and versatile platform but when it comes to K2 database collation you only have a Hobson’s choice. Many product vendors have similar requirements for their back-end databases and there is nothing wrong with that, but problem we had in case of K2 was that a lot of people failed to realize that only one collation is required and supported, i.e. Hobson’s choice was poorly presented to them 🙂

Finally K2 blackpearl compatibility matrix was updated this month to reflect this requirement and I really hope this will clarify K2 collation requirement once and for all. We all agree that at least compatibility matrix is something we all read before rushing into installation or upgrade, right? 😉

So navigate to K2 blackpearl Compatibility Matrix page > SQL Server section notes and… lo and behold this:

blackpearl collation requirement

I hope this will help people to avoid collation related issues from now on and we are all clear that:

Latin1_General_CI_AS collation is required on the SQL server instance hosting the K2 database


Changing SQL collation for deployed instance without reinstall (almost)

IMPORTANT UPDATE: If you use K2 4.6.11 or newer you have to have SQL_Latin1_General_CP1_CI_AS collation on a SQL Server instance which hosts K2 DB (more details)

Collation matters. Period. If you not sure just wait till you get an incident or issue which will teach you that it is. Some apps require you to have specific collation for their back end DB. For example for K2 you need to have  Latin1_General_CI_AS collation (see my earlier post on that _ ).

Nonetheless it can often be the case that your RTFM/Prerequisites_Check moment (I wonder if I can copyright/trademark this term? I heard that Michael Buffer trademarked his “let’s get ready to rumble” phrase _ ) happens after SQL server instance has been already provisioned. For newly created (clean install) K2 database you just need to delete database which was created on instance with wrong collation and allow K2 Setup Manager to create it again after you deal with your instance collation. There is an MSDN article detailing that _ but I will outline the procedure below.

Aforementioned MSDN article says that you have to have installation media to perform this operation, but in fact you can get away with Setup Bootstrap folder which should present on your server in location similar to one below (may vary from version to version, see details _ )

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014

So steps are the following:

1. Locate setup bootstrap folder for your SQL server installation, open CMD or PS window (don’t forget “dot net sourcing” thing for the latter):

2. Execute the following command:

Once this operation completes  master, model, msdb, and tempdb system databases are rebuilt (dropped and re-created) with collation you specified in the command. Obviously all your modifications to these databases are lost (if you made any) and essentially it is sort of reinstall/repair type of operation just much quicker and without need to wade through SQL Setup GUI.


How to: quickly check SQL server collation settings

Just a quick note on how to quickly check Microsoft SQL server collation without wading through SQL Server Management Studio GUI. Go to CMD and execute the following:

\n\n\n[code language=”sql” light=”true”]\nsqlcmd -q "SELECT CONVERT (varchar, SERVERPROPERTY(‘collation’));"\n[/code]\n\n

Alternatively you may use sp_helpsort stored procedure, though when run via sqlcmd it gives a bit strange output if run with sqlcmd

\n\n\n[code language=”sql” light=”true”]\nsqlcmd -q "EXECUTE sp_helpsort;"\n[/code]\n\n

On the boxes with SQL server installed path to sqlcmd executable added to PATH variable so you can fire it off without specifying full path. I guess you may also check corresponding key in registry which should exist there I believe (at least for server collation) 🙂

Also you may view collation settings on databases:

\n\n\n[code language=”sql” light=”true”]\nsqlcmd -q "SELECT name, collation_name FROM sys.databases;"\n[/code]\n\n

Reference: MSDN: View Collation Information