How to change collation as migration

[jouni-kantola]

Should and could we have used databoss for changing a database's collation? How would we do this?

For now, we opted to add a separate create db script, but would've been neat to have had it as a migration.

[drunkcod]

I'd assume that's as simple as this:

declare @sql nvarchar(max) = 'alter database ' + db_name() +  ' collate Latin1_General_100_CI_AI_SC_UTF8'
exec sp_executesql @sql

[jouni-kantola]

We didn't get that to work as a migration script, we're getting this error:

ALTER DATABASE statement not allowed within multi-statement transaction.

We even tried this script with the database name hardcoded and got the same error:

ALTER DATABASE MyDb COLLATE Latin1_General_100_CI_AI_SC_UTF8