Adjusting a database from string with max length of 10,000 to Unlimited

1
Hi there We have been asked to adjust a database string field from a length of 10,000 characters to an unlimited length. When compiling the application locally and clicking the Synchronise database button we get an error: The size (10000) given to the type 'nvarchar' exceeds the maximum allowed for any data type (8000). This is a MSSQL error caused by the Mendix generated code that Drops/Alters/Updates the MSSQL database during the Synchronise database action. Is there a way to force Mendix to write the required SQL statements to update the database field lengths? I don't quite trust my own MSSQL skills. This will have to be done to the tables in both my local database and the client's system. Many thanks
asked
4 answers
2

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.

answered
1

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.

answered
1

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 [endorsement$endorsementtemplatedetail] called [e3314f2bd5064686b1df5119f9a6bace]?

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];
answered
0

https://msdn.microsoft.com/en-us/library/ms176089.aspx

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?

answered