Resolving the Generic SQL Error on Column Definition Update in Dataverse

Recently, I encountered a frustrating error while trying to modify the length of a text field in Dataverse. The error message was: “Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 5074“

Initially, I checked for any dependencies related to my field but found none. After several hours of investigation, I decided to turn to the classic interface of Dataverse.

When attempting to modify the field length through the classic interface, I received a more informative error message by downloading the logs. The logs indicated that another field, “mea_result,” was dependent on the field “mea_input” I wanted to modify.

Here are the Log File details:

Upon inspecting the definition of the dependent field, it turned out to be a formula Column (Fx). As shown in the screenshot, the value of “mea_result” is calculated based on the value of the “mea_input” field.

To resolve the issue, I temporarily assigned a dummy value of the same type (in my case, true) to the “mea_result” field.

Then, I was able to modify the length of the “mea_input” field without any errors. Of course, I didn’t forget to reapply the Fx formula to the dependent field afterward.

I hope this blog post helps you if you ever encounter the same issue. Additionally, if make.powerapps.com doesn’t provide enough detail on a given error, try making the same modification in the classic interface. You might find the answer you’re looking for there ….

Original Post https://xrmtricks.com/2024/07/08/resolving-the-generic-sql-error-on-column-definition-update-in-dataverse/