A get/change operation on table 'GL_Account_MSTR' failed accessing SQL data.

(0) Share

Report

Posted on by adeel333

487

I installed GP 2016 R2 with most of default settings and with customized/required account framework (maximum segments=4). After just 2 days, I realized that this account framework has to be updated to meet future requirement. There were only 2 users created who were entering data in the company. I took backup of company database and DYNAMICS. Then to update account framework, I deleted company database and Dynamics DB and eventually Uninstall Microsoft Dynamics and Microsoft SQL Server 2012 for a fresh start.

I reinstalled SQL Server 2012 and then I installed Microsoft Dynamics (maximum segments=9) and Created New Company (from GP Utilities) with the same name as before. Then I restored previously taken backup company database and started GP after running GP Utilities. I can see all the Inventory data, vendors, customers, purchase orders, transaction but unable to Lookup Accounts in Account Maintenance window and unable to create new Accounts. Similarly unable to explorer Financial section in smartlist.

The Error I received in smart list is: "A get/change operation on table 'GL_Account_MSTR' failed accessing SQL data. More Info: Number of results columns doesn't match table definition.

The Error I received in Account Maintenance when saving Account: "A get/change operation on table 'GL_Account_MSTR' failed accessing SQL data. More Info:

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ACTNUMBR_5'.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ACTNUMBR_6'.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ACTNUMBR_7'.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ACTNUMBR_8'.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ACTNUMBR_9'.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ACTNUMBR_5'.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ACTNUMBR_6'.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ACTNUMBR_7'.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ACTNUMBR_8'.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'ACTNUMBR_9'.

Need expert opinion to resolve this issue. Looking forward for kind feedback.

Categories:

Microsoft Dynamics GP

All responses (4)

Answers (1)

Verified answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (1)

Report

RE: A get/change operation on table 'GL_Account_MSTR' failed accessing SQL data.

Hi Adeel,

This is typical when you try to restore a GP instance over a newly setup configuration where you take only the company DB's and not the DYNAMICS DB.

As Richard said, the account framework that is defined the DYNAMICS DB needs to match the one on each company for the base framework, meaning that even if the actual COA doesn't use all the segments, the tables have to be created accordingly to the max defined in the DYNAMICS system DB.

I ran into a case where a client had crashed their SQL server and were only able to recover the companies, not the system DB (DYNAMICS).. it was a nightmare until we had fixed all the tables according to the new DYNAMICS system setup that we had created on the new server.. it took several days and as David said, it's much easier to perform with a tool like GP Power Tools Database Validation or CRG Account reformater..
David Musgrave MVP ... 14,053 Most Valuable Professional on at

Like (1)

Report

RE: A get/change operation on table 'GL_Account_MSTR' failed accessing SQL data.

GP Power Tools Database Validation can update table structures to match the dictionary table definitions.

Regards

David
adeel333 487 on at

Like (0)

Report

RE: A get/change operation on table 'GL_Account_MSTR' failed accessing SQL data.

ohh, ok. I got your point. Thanks for your feedback.
Richard Wheeler 75,848 Moderator on at

Like (2)

Report

RE: A get/change operation on table 'GL_Account_MSTR' failed accessing SQL data.

Contact Corporate Renaissance about their Reformatter product. (https://crgroup.com/). The account framework gets baked into several tables not only in the DYNAMICS database but also your company databases. You cannot do what you are trying to do. There are ways but it would involve a lot of work.