search

erikbra / grate

grate - the SQL scripts migration runner
MIT License
209 stars 40 forks source link

Unable to use grate on UTF-8 collations #591

Open SamuelMcAravey opened 4 weeks ago

SamuelMcAravey commented 4 weeks ago

Describe the bug Unable to use UTF-8 collations with SQL Server due to using the obsolete text column type.

To Reproduce Create a new database using a UTF-8 collation, such as Latin1_General_100_CI_AS_SC_UTF8, and run grate against this database.

Expected behavior A successful migration happens.

Screenshots

image

Desktop (please complete the following information):

Additional context When running this through the debugger, I get this specific exception which indicates that this is due to the text type:

'Column or parameter 'text_of_script' has type 'text' and collation 'Latin1_General_100_CI_AS_SC_UTF8'.
The legacy LOB types do not support UTF-8 or UTF-16 encodings. 
Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags.'

In my local version I changed all of the text types to nvarchar(max) and everything functioned as expected.

There appears to have been some prior discussion here, but it appears that the column type was not updated: https://github.com/erikbra/grate/issues/250