Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

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

(0) ShareShare
ReportReport
Posted on by 485

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:
  • Verified answer
    Beat Bucher  GP Geek  GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    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 GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 13,932 Most Valuable Professional on at
    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 Profile Picture
    adeel333 485 on at
    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 Profile Picture
    Richard Wheeler 75,730 on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans