K2 Database Collation Change

Introduction

Need a new collation change for your K2 database? If your answer is yes, you found the right article!

In the past, K2 did not specify a standard collation to be used on their database during installation. However, K2 introduce the collation standard to be used some time after (If i am not wrong it is during 4.7).

Thus, the problem comes in where your database is not on Latin1_General_CI_AS. When you move to a new SQL server and set the default to Latin1_General_CI_AS, the database will goes craze. The main components like the table columns, views and user defined tables will all be affected. For these main component, the sub components like stored procedures, indexes, functions and etc, are all affected as well.

So this article will guide you on changing the columns collation.

NOTE: This is documented based on K2 4.7 database migration

Target Audience

You must understand on the managing the SQL and have went through K2 installation before.

How it works

Rules & Rules

The collation only apply to certain columns which are your text columns like nvarchar, varchar and many more. So we will have to focus on these columns to alter the collation.

To alter the column’s collation, there are several rules we have to abide to. These are the rules that i had came across:

  1. Column must not be indexed (That would apply on your primary key, foreign key, and constraint key as well)
  2. Computed column must be dropped (You cannot alter the computed column collation)
  3. User defined table must be dropped (You cannot alter the column directly)

Preparation

A must thing to do, database backup!

I would recommend to create 2 folders named it Drop and Create.  The scripts are actually being segregated as we will have a clearer picture on what we will be executing and what sequence should we be running it. That is to say, if you are confident of the scripts, you can actually combine them together. (Do take note, it is actually a very huge script if you combined them altogether)

Generating Scripts

These are the list of scripts that we will need to prepare prior to this exercise:

  1. Dropping of indexes, PK, FK & CK
  2. Dropping of computed columns
  3. Dropping of Stored procedures, views, user defined table and function
  4. Creation of indexes, PK, FK & CK
  5. Creation of computed columns
  6. Creation of Stored procedures, views, user defined table and function
  7. Alter column collation

The tricky parts comes in for dropping/creation of indexes and alter column collation where you do not have a out of box function cater for it. Thus, we will need to run a script to generate out the statements.

FYI, the sequence of generating the scripts will not be the same as the below guide, thus just generate it according to the steps and we will see the final outcome.

From SQL Management Studio

Let us start off with something easy. First we will generate the drop and create script separate for the following:

  1. Drop script for SP, View, Function and User Defined table
  2. Create script for Function, View and User Defined table
  3. Create script for SP

We will do it via SQL Management Studio, so lets begin!

Generate Drop Script

  1. Go to SQL Management Studio
  2. Go to the K2 database > Right click > Tasks > Generate Scripts
  3. Select Views, SP, User Defined Functions and User Defined Table Types and click on NextGenerate Drop scripts.png
  4. Click on the Advanced button, select Script Drop optionGenerate Drop scripts
  5. Once done, generate the script. Save it in the Drop folder and named it ‘6. Drop All SP View Fn UDT.sql’

Generate Create Script for Function, View and User Defined table

  1. Follow the steps as above but select only Views, User Defined Functions and User Defined Table Types
  2. For Script Drop option, select Script Create
  3. Once done, generate the script. Save it in the Create folder and named it ‘3. Create View Fn UDT.sql’

Generate Create Script for Stored Procedures

  1. Follow the steps as above but select only Stored Procedures
  2. For Script Drop option, select Script Create
  3. Once done, generate the script. Save it in the Create folder and named it ‘5. Create SP.sql’

Up till here, you should have a total of 3 scripts generated.

From Scripts

Generate Scripts – Drop and Create

This is a tedious part where you would need to generate the rest of the scripts.

1. Drop FK

2. Drop Index

3. Drop PK

4. Drop CK

6. Create CK

7. Create PK

8. Create Index

9. Create FK

Once you have downloaded the above scripts, you may execute the scripts and save it as per the file name. Steps as follows:

  1. Open ‘1. Drop FK.doc’ file, copy the content, paste to the SQL Management studio
  2. Ensure you are on K2 database and execute itGenerate Drop scripts
  3. Once executed, the statement will be generated to the messages box (in green as above). Copy out the text and save it as per the doc file name but in .sql extension.
  4. File name containing ‘Drop’ text, save it in the Drop Folder. File name containing ‘Create’ text, save it in the Create Folder.

Generate Scripts – Alter column collation

This is slightly more complex where you will need to do some changes after the script is generated. Please download 2. Alter Collation .

  1. Open ‘2. Alter Collation.doc’ file, copy the content, paste to the SQL Management studio
  2. Confirm the collation name parameter (@CollationName nvarchar(255)) is the correct value. (This should be the collation name that you want to change to)
  3. Ensure you are on K2 database and execute it
  4. Once executed, the statement will be generated to the messages box. Copy out the text and save it as per the doc file name but in .sql extension.
  5. Find the following four sentences and comment/delete it:
    • ALTER TABLE [Identity].[Identity] ALTER COLUMN [DisplayName] nvarchar(448) COLLATE…
    • ALTER TABLE [Identity].[Identity] ALTER COLUMN [Email] nvarchar(128) COLLATE…
    • ALTER TABLE [SmartBroker].[SmartObject] ALTER COLUMN [Name_XML] nvarchar(450) COLLATE…
    • ALTER TABLE [SmartBroker].[SmartObject] ALTER COLUMN [DisplayName_XML] nvarchar(450) COLLATE…
  6. Save it in the Create Folder.

In 4.7, the commented four statements are actually four computed columns in the table. This will be handled in another script later on.

Standard Scripts

I will be providing the rest of the 3 scripts that will be needed for this exercise.

1. Update Database Collation

To be saved into create folder with .sql extension, ensure that the K2 database name is correct.

4. Create Column

To be saved into create folder with .sql extension. Please take note, the columns are tested in 4.7, this could be different for other version.

5. Drop Columns

To be saved into drop folder with .sql extension.Please take note, the columns are tested in 4.7, this could be different for other version.

Files Summary

After going through the above you should have the below for:

Drop Folder:Generate Drop scripts

Create Folder:

Generate Drop scripts

Execution

As per the file naming, you will execute based on the sequence starting from 1. Starting with the drop folder then follow by the create folder.

Once your execution has complete, the affected columns should have updated to the collation. Do a quick check by checking on the column properties.

Hope this article helps for this painful exercise.

Others

Although this article in on K2 database, the some of the scripts can also be used when you are changing collation on your other databases. You just need to keep a lookout on the few aspects (computed column, function and etc) during the exercise.

Advertisements

3 comments

  1. These scripts did not work in K2 v4.7, you will have to upgrade to v5.0 There’s also an error on
    Drop Function [Category].[GetCategoryFullPathById]:

    Error:
    Msg 3729, Level 16, State 1, Line 2
    Cannot DROP FUNCTION ‘Category.GetCategoryFullPathById’ because it is being referenced by object ‘Data’.

    This is a calculated column that you’ll have to include in your drop and create column scripts

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s