Skip to main content
Microsoft Dynamics GP forum
Answered

GP new Installation Account framework issue

editSubscribe (0) Share
ReportReport
Posted on by

I am done with the GP Installation of GP 2016 R2 on a new server. I had previously started setting up this Company under our current GP installation and Restored its GP company DB after installation was complete. It looks the maximum account lengths and maximum segments do not match and I am getting the Error below. Any suggestions as how to continue from here? There is no important data in this new company and I can Re-Install GP if I have to. The Accounts setup on the right is the new Installation.

Thanks in advance,

Shai

capture_5F00_gp.JPG

  • David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 13,781 Super User on at
    RE: GP new Installation Account framework issue

    If you want to recreate users, use GP Power Tools Database Validation tool. Much better than the using that script.

    Regards

    David

  • Suggested answer
    David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 13,781 Super User on at
    RE: GP new Installation Account framework issue

    To update a client to match the data in the system database change dex.ini setting Synchronize=TRUE

    Regards

    David

  • adeel333 Profile Picture
    adeel333 485 on at
    RE: GP new Installation Account framework issue

    Richard your expert suggestions help me a lot. However, I am stuck in the following scenerio:

    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'.

    If you please be so kind to guide me how to resolve this issue?

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP new Installation Account framework issue

    Using SSMS you can delete databases. Deleting users per databases is done by clicking the plus sign next to the database and then Security then Users.  If you run the capture logins script from KB878449 you should not have to do this.

  • adeel333 Profile Picture
    adeel333 485 on at
    RE: GP new Installation Account framework issue

    @Richard Wheeler, if you can please explain that how to delete DYNAMICS and Company DB from server, you mean from SQL Server Management Studio, Under Databases, right click on Dynamics and Company DB and Delete?

    Also if you can kindly let me know about step 5, Using SSMS to remove users from company database, how to do that?

    I'll highly appreciate your kindly feedback.

  • shai4444 Profile Picture
    shai4444 on at
    RE: GP new Installation Account framework issue

    Thank you Richard! That worked great!

  • Verified answer
    Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP new Installation Account framework issue

    OK, here is what you can do:

    Step 1) Delete DYNAMICS and company databases on new server

    Step 2) On new server, launch GP Utilities and when asked about segment setup use the same settings as old server.

    Step 3) Create company database using GP Utilities

    Step 4) Restore company database over newly created company database

    Step 5) Using SSMS remove users from company database.

    This will give you a new DYNAMICS database with no users. You may want to run the NoteFix utility if your users use notes in GP.

    You may also need to deploy your SSRS and Excel reports.

  • shai4444 Profile Picture
    shai4444 on at
    RE: GP new Installation Account framework issue

    Thank you for the answers and follow-up! The selects above for the Dynamics system tables confirms that the values are different as you can see from the Image in the beginning with my question. I do not need the 5th segment at all. The DYNAMICS.DIC file datetime stamp is changing. All this brings me to your proposed solution of deleting the databases, that should not be a problem. I am hesitant to restore the Dynamics database. The original one is for a separate location and has been used for a long time. I don't want to bring over any users or any setup. It sounds like I might be better off Re-Installing this GP Instance.

    How would the maximum values change? by restoring the Dynamics Database?

    Thanks,

    Shai

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP new Installation Account framework issue

    Wait a minute. Are you restoring a GP company from another GP instance that had a different account structure? If so, this will never work. The default account structure is kept in the DYNAMICS database and each company must match what is in there. Within each company you can make changes to the account structure but the base account structure can not be different. What are the results of these queries?

    SELECT * FROM DYNAMICS..SY003001

    SELECT * FROM DYNAMICS..SY00302

    Also, make sure the datetime stamp on the DYNAMICS.DIC file is changing. You could try launching GP Utilities as administrator to make sure this is not a security issue.

    Worse case scenario is to delete all databases on the new server. Go into the existing GP instance and then Tools->Setup->Company->Account Format. Make note of the maximum account length and maximum segments.  Then launch GP Utilities on new instance and use these maximum values and then try to restore. You will need to restore your company databases as well as the DYNAMICS database. This is assuming the new server has not been brought on line yet as your are restoring all databases. Do you need the 5th segment? If not, run GP Utilities using the maximum settings from the original installation. If it is needed, contact Corporate Renaissance and get their Reformatter tool as you need to add a segment.

  • shai4444 Profile Picture
    shai4444 on at
    RE: GP new Installation Account framework issue

    I have tried that already and it didn't change anything. I just tried again with the same results. It appears that GP is expecting another column in the GL accounts table and it is not there. That makes sense because the account framework is set to 5 Segments and I restored a company that has 4 segments.

    Capture-gp2.JPG

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Sundar…

Industry experts answer burning questions directly from our amazing Community…

Enabling Copilot Case and Conversation…

Agents can easily recap an ongoing chat, transcribe a voice conversation…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 282,978 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,562 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans