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
Sage Transform VIP Experience
Live Event
VIP luncheon for top finance executives attending Sage Intacct 2022.

October 12, 2022 | 09:30 AM - 10:45 AM PT
Women in Life Sciences: Women’s Health & Wellness
Webinar
Spill the Tea With Delphine O’Rourke on the State of Women’s Health

October 11, 2022 | 02:00 PM - 03:30 PM PT
ASC 842: Lessons Learned From Early Adopters
Webinar
Get the scoop from early adopters who have implemented ASC 842.

October 6, 2022 | 09:00 AM - 10:00 AM PT