I recently encountered a strange error in AX, where on opening a particular form, AX would throw an error saying “The fetch type refresh cannot be used with forward only cursors”.
Restarting the AOS, running full X++ recompile and full CIL generation did not resolve the problem.
“The fetch type refresh cannot be used with forward only cursors”
What’s causing it?
However, I tried to open the table used by the form in the table browser, and I got the exact same error. So, looking into it a bit further, this table had a notes field which for some strange reason was showing up in the database table as an NVARCHAR(10) field. That should have been an NVARCHAR(MAX) field.
AX:
SQL:
OK, that’s strange… Let’s tell SSMS to refresh the fields. No go, same thing…
It just so happens that NVARCHAR(10) is what AX will create as default when creating a new string field, but this was not a new field. Something would have happened internally in AX to confuse it. Not too unusual, and just one of the many reasons why development should never be done directly in a production AX environment.
If you want to recreate this issue for yourself, change the definition of one of your notes fields in the database to be a bounded string instead, for example 10 or 20 characters long. Once done, try to open the relevant table in the table browser.
Solution
So, it turns out this is just AX telling you “I tried to retrieve a large value field but it wasn’t large value after all.”
Anyway, fixing it is easy enough, just synchronise the table. If the synchronisation doesn’t work, try to change the field back to NVARCHAR(MAX) in SQL. Also make sure that the relevant record in SQLDICTIONARY is reflecting the expected field type and length.
Alternatively, grab a new backup from the production database and restore into your development environment, then try again.
Conclusion
Luckily I caught this in a development environment, so no data was lost. If the field would have scaled back to an NVARCHAR(10) in a production environment, any contents longer than 10 characters would have been truncated and lost, and you would need to recover the contents from backups.
If you do get this in development, test or staging, immediately make sure the field is defined correctly in the production database and elsewhere. Catch it as early as possible to reduce the chance of data loss.
Also, before releasing your application to production, make sure you have a fully up to date backup of the database and that it is available to you to quickly and easily restore in case of problems. As soon as your application has been deployed, synchronise and check that the affected field is still the expected data type and size in SQL Server.