Changing the collation of the Octopus database

By default, the Octopus database is created using Latin1_General_CI_AS collation.

You can change the collation, or create a database initially with a different collation.

Changing the collation must be done with care. Changing a SQL Server Database’s collation does not change the collation of existing user-created objects within.

You must ensure you also change the collation of all objects in the Octopus Database, otherwise errors can occur when modifying the database during Octopus version upgrades. New objects created will use the updated collation, and when attempting to (for example) perform SQL joins between these and existing objects using the original collation, collation mis-match errors may occur.

For this reason, when modifying the SQL Server Database during Octopus upgrades, Octopus will verify that all columns in the database use the same collation as the database itself. If they do not, an error will be logged and the upgrade will be prevented from taking place. This is to ensure you can rollback, or correct the issue and continue, without the database being left in an invalid state.

Errors during Octopus Server upgrades

Database update prevented: One or more columns in the database are not using the default collation

If you have received the error above while upgrading your Octopus Server, it is likely that at some point the collation on your Octopus database was changed without changing the collation of the existing objects.

The following SQL can be executed against your Octopus database to identify any columns which do not use the database’s default collation:

Identify columns with non-default collation

DECLARE @DatabaseCollation VARCHAR(100)

SELECT
    @DatabaseCollation = collation_name
FROM
    sys.databases
WHERE
    database_id = DB_ID()

SELECT
    @DatabaseCollation 'Default database collation'

SELECT
    t.Name 'Table Name',
    c.name 'Col Name',
    ty.name 'Type Name',
    c.collation_name
FROM
    sys.columns c
INNER JOIN
    sys.tables t ON c.object_id = t.object_id
INNER JOIN
    sys.types ty ON c.system_type_id = ty.system_type_id    
WHERE
    t.is_ms_shipped = 0
    AND
    c.collation_name <> @DatabaseCollation

(script taken from StackOverflow)

To resolve the issue, either alter the columns reported by the script above to match the database’s collation, or alter the database’s collation to match the existing columns (assuming all columns are listed).

Some of the issues in changing the collation of an entire database are discussed in this question.

Help us continuously improve

Please let us know if you have any feedback about this page.

Send feedback

Page updated on Sunday, January 1, 2023