Armanino Blog

Resolving SQL Errors in Dynamics AX 2012

January 30, 2013

Are you getting an infolog with the following message: "Cannot select a record in ... The SQL database has issued an error?" If so, this indicates that there is some discrepancy between the table definition in SQL and the definition in AX 2012.

The table defined in AX is actually a buffer that will be used to store and work with the data returned from the table. Dynamics AX formats a SQL command based on what it thinks the table looks like and the command may fail if the actual table is different.

This often happens when the data in a test instance is refreshed from the production environment and the definition of a table in the test environment has been altered. In this case you should be able to fix the problem by resynchronizing. This process will modify the SQL table to match the definition in AX.

To resynchronize, go to System Administration>Periodic>SQL administration. I always select "All Tables" and under table actions click on "Check Synchronize." This will take a few minutes to run. During the process you may get warnings. Read the warnings carefully and consider the implications before you tell it to continue.

For instance, if a unique key index has been added, the program will warn you there may be duplicate key values. The program does not check the data for this—it's merely warning you that it's possible. In many cases this might be ok to ignore and continue on, but if you get a more serious message such as it needs to drop the data from a table in order to implement the change, this is more serious. By continuing, you will lose the data in this table.

With that said, in most cases the synchronization works fine and will resolve the SQL error message you are getting within the infolog – just be sure to read warnings that may appear during the process carefully.

Stay In Touch

Sign up to stay up-to-date with the latest accounting regulations, best practices, industry news and technology insights to run your business.

Related News & Insights
Building Beyond: Exploring Construction Trends for Growth
How to Build a Better Foundation for Your Company

June 13, 2024 | 10:00 AM - 10:30 AM PT
2024 Digital Marketing Trends – What's Now, What's Next?
Stay up to date on the latest trends and strategies in digital marketing.

May 30, 2024 | 11:00 AM - 11:30 AM PT
Risk Mitigation Measures: Navigating Disruption with Confidence
Is your business at risk from unseen dangers?

May 29, 2024 | 12:00 PM - 01:00 PM PT