Added new fields into Dataverse table and getting errors when opening Model Driven Form and running flow

[sammysword]

Hello,

I have done this process a million times before and today for some reason I am getting an error. I have a series of Dataverse tables which are used to manage a Model Driven app and which have Flows running off them. I added a couple of new fields into the table, then added these into my Dataverse Form. When I now try to open the form or run any workflow against them I get an error as shown below. Has anyone seen this before?

"Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 207"

[LinnZawWin]

I haven't seen such an error but such a "Generic SQL error" can probably only be able to be investigated/resolved by Microsoft Support.

[MHanna]

My tight deadline project is in jeopardy because this problem has just started showing up for me. I simply added a new column that is a lookup for another table. Using the web api I see "A" problem:

https://orgxxxxx.crm9.dynamics.com/api/data/v9.0/xxxx_fitness?%24filter=createdby%2Fpreferredemailcode+ne+null&%24expand=xxxx_Manager%2Ccreatedby 

{"error":{"code":"0x80044150","message":" Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 207"}}

If I remove the ' xxxx_Manager' part of the expand query referring to the lookup table I get a perfectly fine result.

[LinnZawWin]

What is the Web API query before the removal of the 'xxxx_manager' part of the expand query?

[MHanna]

Do you mean after the removal of 'xxxx_Manager'? Well, in that case, it is just a JSON dump of the dataverse table xxxx_fitness. There are just two records in there that are test data. What is missing would be the extra data for the records associated with 'xxxx_Manager'. I think Microsoft uses expand to prevent multiple queries since they can guess what the next query would be. I can't make Filter not do the expand thing.

If you actually mean 'before' removal of 'xxxx_Manager' then that would be the error message shown under the url.

If you mean the url itself... Well, it is the web api url to the dataverse table xxxx_fitness. The query is the filter that is the actual request being made. Basically, it is Filter('xxxx_fitness', IsBlank('Created By'.'Primary Email')) and expand tries to get additional record information for lookup columns that Microsoft thinks will be needed.

[sammysword]

Yeah it seems to be as clear as any new fields that are added to any query is causing the issue. If I remove my fields from my forms then I can display the form fine, but as soon as I add new fields in then I get an issue. Strangely though this isn't the case form Views and I can add new fields into the View and it works fine.

[sammysword]

As another development, I have exported this as a managed solution and pushed it into a new Environment which was also in a new Tenant, and the issues are not showing there. So it looks like there may be something Environment or Tenant specific that is the issue.

[MHanna]

Yes, I had to export my Solution, delete it from the environment, and then re-import it. Somehow that fixed the problem. Ugggg....

[sammysword]

Do you think this would cause any issues the next time I promote to Production if I done this? The issue I'm seeing is currently in my Dev so I suspect if I do what you done I'm going to have to delete all components manually (as my solution is Unmanaged) and then re-import them. But my concern is this will have an impact when I next import to FAT, Test, Prod

[MHanna]

I haven't deployed a Power App yet but, well, I think going to production is going to be safe. Nothing is in development and anyone currently using the product will still be accessing the previous version. Anyone who starts using the product later will be using the new version. The tables and columns are all versioned as well so it should be fine.

I only have a theory about how this problem happened for me... something got locked and failed to update.

I had my canvas app running in another tab and I just closed the tab so I could make a change. I’ve noticed now that when I just close the canvas app designer tab then the app is locked because Power Apps thinks it is still in use. For me at least, if I try to go back into editing the app I’m often told that it is being edited and do I want to override or takeover. In retrospect, I should have just backed out of the designer effectively closing it before closing the tab – or I could have just kept using that tab for the dataverse work.

When I went into the dataverse after closing the designer tab and added a column to a table for my new feature the app was still locked. After I published that change, Power Apps had the table flagged as already being used and I think this locked a portion of metadata from being updated. FYI, all tables and columns in the dataverse are just metadata or virtual tables and columns. Without that metadata being changed the new column simply didn’t exist somewhere in the system though other parts could see it.

Another idea I have is that the query part of the dataverse was still trying to use the previous version of the table though a newer one had been published. It is like that part of the dataverse didn’t know about the new version with the new column. Maybe it would have fixed itself over time. Power Automate can be slow to make changes too and I'm sure they use that. Who knows.

Exporting and deleting the solution basically removed everything of course. Re-importing the solution added all the necessary metadata this time because nothing was blocking it. Just my theory.

In the future I intend to make sure nothing is open / locked before publishing a dataverse change while doing development. I’m sure publishing a new canvas app isn’t going to be a problem. SO FUN!