Armanino Blog
Article

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.

Resources
Related News & Insights
IPO Prep & SOX Compliance: Instacart & Armanino Share Hard-Earned Knowledge
Webinar
Know your compliance requirements today to avoid obstacles tomorrow.

December 16, 2021 | 11:00 AM - 12:00 PM PT
General Contractor Trends to Consider in 2022 Webinar
Webinar
Hear from experts how you can better manage your subcontracts — and more.

December 16, 2021 | 10:00 AM - 11:00 AM PT
Why COP26 Matters for Your Business Webinar
Webinar
Get informed of COP26 developments and how you can act now.

December 15, 2021 | 10:30 AM - 11:00 AM PT