Hmmm... I would be hesitant trying to perform this change directly on the database (and of course this would need to be done separately in all your environments). I would consider creating a new unlimited text field, then a microflow to copy the data from the old field to the new by looping through the records. As this is a large text field, I assume it's not deeply involved in your workflow logic, so it should be simple to display the new field instead of the old once the data is successfully copied. Finally you can delete the original field.
This approach requires a few stages and updates, but would allow you to check you have lost no data before proceeding to the next stage.
Thanks for the speedy reply, Jaap.
If I set the string length to anything <= 10000, it compiles ok. It's only once I set it to unlimited that the sync database produces this error, so I'm definitely setting it to unlimited.
The code that the sync database performs (or tries to) is as follows:
EXEC sp_rename '[endorsement$endorsementtemplatedetail].[wording]', 'e3314f2bd5064686b1df5119f9a6bace', 'COLUMN'; ALTER TABLE [endorsement$endorsementtemplatedetail] ADD [wording] nvarchar(max) NULL;
UPDATE [endorsement$endorsementtemplatedetail] SET [wording] = CAST([e3314f2bd5064686b1df5119f9a6bace] AS nvarchar(10000));
ALTER TABLE [endorsement$endorsementtemplatedetail] DROP COLUMN [e3314f2bd5064686b1df5119f9a6bace];
I have omitted the last two DB update statements. The offending line is in bold above.
The update statement should have been generated with a CAST to nvarchar(MAX), not 10000. I'm guessing that happened because your old field size was 10K. Since MSSQL only supports up to 8K before using a MAX field, I think Mendix correctly generated the field as a nvarchar(max), but then didn't apply that same logic when creating scripts for data conversion.
Anyway, I don't think you actually even need the CAST in MSSQL. Do you still have a field in
If so, try running this manually in SQL Server. Make sure the first executes correctly before running the second (or you'll lose your old data):
UPDATE [endorsement$endorsementtemplatedetail] SET [wording] = [e3314f2bd5064686b1df5119f9a6bace]; ALTER TABLE [endorsement$endorsementtemplatedetail] DROP COLUMN [e3314f2bd5064686b1df5119f9a6bace];
n has to be <=8000. So are you sure you are setting it to "unlimited" and not 10000? I would expect Mendix to generate a nvarchar(MAX) statement.
Otherwise: is it possible to list (the relevant parts of) the database synchronization file Mendix has generated?