Skip to main content

Notifications

Microsoft Dynamics GP forum
Answered

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

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.

  • Verified answer
    Beat Bucher  GP Geek  GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,019 Moderator 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,831 Moderator 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

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,524 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,469 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Product updates

Dynamics 365 release plans