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
Sustaining SaaS Success: Extending Runway and Mastering Cash Management
Cash Management Strategies for SaaS

December 13, 2023 | 10:00 AM - 11:00 AM PT
Simplifying the California Climate Accountability Package for Businesses
Master California’s New Climate Laws

December 12, 2023 | 10:00 AM - 11:00 AM PT
Microsoft Warehouse Management: Optimizing Healthcare Supply Chains
Experience how Microsoft D365 optimizes healthcare warehouse management.

December 7, 2023 | 11:30 AM - 12:30 PM PT