We are facing an SQL error with saving property values in Umbraco v7.15.6.
We checked with the database, the values try to save in dbo.cmsPropertyData under dataNvarchar property. the property set as nvarchar(500) , that's the issue, once the input valued limit exceeds 500 it will throw the error.
We can directly update the field datatype in SQL direct query, but we are not preferred that, is there any other ways to handle this, or
Is this issue fixed in some other Upgraded Umbraco versions, If so we need the upgraded Umbraco version name details?
[API] Exception was thrown in class 'SqlConnection' while invoking method 'OnError', with the message: 'String or binary data would be truncated.
The statement has been terminated.'.
System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
Hey, not sure if you're still working on this, but I ran in to a similar issue on 8.12. I was bumping up against that nvarchar(512) limit, but only occasionally and only by a relatively slim margin. I noticed that the JSON object was being stored with whitespace I didn't need, so I stripped that out by stringifying my object when it was submitting instead of submitting just the rendermodel.
Your mileage may vary depending on what your object looks like, but in my case the built-in minification from JSON.stringify() was enough to solve my problem.
Is there any concern with existing data when that type is changed to text? I had considered that approach, but was worried about unforeseen consequences.
For what it is worth, or next time I Google this... it seems fine to change the 'valueType' from 'STRING' to 'TEXT'.
I did this via the package.manifest but I also manually updated the 'cmsDataType' table as I believe this is only written to when the property editor is first installed.
This process appears to be non-destructive. In fact querying ''cmsPropertyData' it is possible to see old values saved in the 'dataNvarchar' column, and new values saved after making the above change in the 'dataNtext' column.
String or binary data would be truncated
Hi Team,
We are facing an SQL error with saving property values in Umbraco v7.15.6.
We checked with the database, the values try to save in dbo.cmsPropertyData under dataNvarchar property. the property set as nvarchar(500) , that's the issue, once the input valued limit exceeds 500 it will throw the error.
We can directly update the field datatype in SQL direct query, but we are not preferred that, is there any other ways to handle this, or
Is this issue fixed in some other Upgraded Umbraco versions, If so we need the upgraded Umbraco version name details?
Hey, not sure if you're still working on this, but I ran in to a similar issue on 8.12. I was bumping up against that
nvarchar(512)limit, but only occasionally and only by a relatively slim margin. I noticed that the JSON object was being stored with whitespace I didn't need, so I stripped that out by stringifying my object when it was submitting instead of submitting just the rendermodel.I went from this:
to this:
Your mileage may vary depending on what your object looks like, but in my case the built-in minification from
JSON.stringify()was enough to solve my problem.I've ran into this when defining a custom data type and the valueType is set to STRING.
The fix was to set this value to TEXT. When I made this update, the value was stored as
ntextand had plenty more than 500 characters.Documentation for this here.
Good to know, thanks for pointing that out!
Is there any concern with existing data when that type is changed to text? I had considered that approach, but was worried about unforeseen consequences.
For what it is worth, or next time I Google this... it seems fine to change the 'valueType' from 'STRING' to 'TEXT'.
I did this via the package.manifest but I also manually updated the 'cmsDataType' table as I believe this is only written to when the property editor is first installed.
This process appears to be non-destructive. In fact querying ''cmsPropertyData' it is possible to see old values saved in the 'dataNvarchar' column, and new values saved after making the above change in the 'dataNtext' column.
Chris
Umbraco version 7.15.6
is working on a reply...