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
Stress-Free 1099 Filing for 2025: What You Need to Know
Webinar
Don't let year-end stress catch you off guard.

November 21, 2024 | 12:00 PM - 01:00 PM PT
Overview of Sage Intacct Real Estate
Webinar
Get an inside look at Sage’s platform built for the real estate industry.

October 16, 2024 | 10:00 AM - 11:00 AM PT
Mastering FP&A for Modern Enterprise
Webinar
Transform Your FP&A Approach to Drive Efficiency and Strategic Impact

October 10, 2024 | 12:30 PM - 01:00 PM PT