Collation error in the workspace after upgrading to 807
I've been working on a client site and one of the things I am working on is planning and testing for an upgrade to K2 [blackpearl] version 807 from version 803. The upgrade testing has gone smoothly so far except for an error we experienced in the workspace. The error is "cannot resolve the collation conflict between Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS in the like operation".
I did some investigation and this not a K2 [blackpearl] error. In effect this boiled down to the SQL server's collation being changed at some point in time after K2[blackpearl] was installed. There is a temporary table being used in generating a work-list in the workspace for 807 and these two facts combined to mean that the tempdb has a different collation to the columns in the K2 server DB and hence the error.
I decided the best resolution to this issue was to massage the collation on all the columns in the K2Server DB.
To do this I wrote a SQL script which I have attached to this blog post. All my initial testing with the client suggests this resolves the collation issue.
Hopefully anyone else running into this issue will benefit from this solution.
The attached script is supplied as is, I encourage you to back up any databases you apply this to and to test the script rigorously before applying this in production. The script is only attended to change the K2Server database, however the core could be adapter for other collation manipulate needs.
25th November, 2008 - Update:
I've update the attached script to a zip file containing scripts for all the K2 databases. I've excluded the SmartBox database as it's indexes are different for each customer's specific implementation, however the scripts supplied can form a model for which a custom SmartBox migration script can be created. I have also included a script for the Workspace database, however the index drop and create statements will need to be customised for your specific database as the index names are not the same for all implementations. I have also updated the scripts to include a statement to alter the default collation on the database itself and not just the columns within the database.