Posts Tagged ‘Dyanmics GP’

Why my GP is too slow?

Hello all, I would like to provide you with some information on what to look when 312339_117542088417759_1094173370_nDynamics GP seems to be working slow. As you may already know Dynamics GP is a process driven application and you may experience slow performance when specific processes are performed in GP please take a look:


· While posting might be due to the PJOURNAL table as you know checks post too or remittance is being printed separately

· Client workstations should have a default printer setup and online, remove invalid printers

· While opening windows, the autocomplete feature may cause performance issues and if not used can be turned off

· While login into Dynamics GP or utilizing 3rd party dictionaries as well if Menu Master table (SY07110) became too large.

· The location of the modified dictionaries other than local workstation

· Certain Smartlist reminders might interfere with login into Dynamics GP

· You may have shortcuts to network locations that are no longer mapped or available

· Printing to file directly into the client/remote computer instead of the hosted server user folders

· OLE Notes path in Dex.ini

· SQL AutoClose and AutoShrink options not set to false

· Virus scanner setup not excluding the following extensions (CNK, DIC, CHM, SET, INI, DAT, IDX, VBA, LOG, LDF, MDF)

· The Dynamics GP homepage smartlist favorites

· The Dynamics GP homepage outlook integration

· Enabling tracing options in DEX.ini

· Bad user defined triggers in SQL

· Bad configuration of SQL server memory allocation

· SQL server or Dynamics GP server available disk space

· SQL server log file is full and is not set to Autogrow

· TNT*.* files, your %TEMP% folder has not been cleaned

· Non-compliant SQL server/GP Server/Client hardware

· Different DB owner than DYNSA

· Little or no SQL server maintenance (Table Fragmentation)

· You might be missing table indexes or statistics

· When exporting a budget, thru the budget wizard it seems locked (if you are using the excel wizard to export, make sure the “save as” dialog is not on the background, Alt-Tab to it as it must have been opened and its behind your main GP windows.

· Too much history (You can archive historical years, specially if you have large tables like Item Master, Customers, Vendors) Believe me I ran reconcile one time and it took 6.5 days on a company with more than half a million SKUs and 5 years of sales.


I have witnessed how few administrators that in order to preserve enough disk space they have a tendency of running SHRINK on the SQL server, this obviously will fragment tables affecting performance. I have a post that covers that here.


If you want us to take a look at your environment don’t forget to contact us, and as always when troubleshooting record answers for the following questions:

1.- Can you replicate the issue? write down steps that let you reproduce the issue.

2.- If its related to posting, please note the module( s ), how many transactions are in the batch, how long does the process last? how long did the process last before?

3.- On a Server/Client install, can you replicate on the server?

4.- Can you reproduce on all or other clients?

5.- Are there any 3rd party products running on the same SQL/GP server or together with Dynamics GP?

6.- Are there any customizations in GP?


Until my next post and let us know if we can help!!

Francisco G. Hillyer

Bug 64711: Year End Close Report is not balanced

409758_4438817855248_1894156222_nI had the opportunity to work on a case where the Year End Close report was being printed with the Debits not matching the Credits. No its not the JE created after a GL close, but the report that lists all P&L accounts and the retained earnings account.


Customer showed me how “before the upgrade” all YEC reports matched and “after the upgrade” they were not in balance.

I inherited this case from my friend Doug Pitcher and my friend Mike Feori.

The oddness of the case made it challenging to resolve, however I try as much as possible to adhere to the “Microsoft Dynamics 10 Rules” rule number 6 says: “Search for a workaround when final solution can’t be provided or is delayed”.

Here is the information I used to recreate the scenario and its improved with comments from Microsoft CTS.


The issue has been written up as Bug # 64711 “Divisional Account Segment information not updated in GL40000 table if you disable in the General Ledger Setup window”.

Here are the steps that can be taken to recreate the problem:

1.  Go to the General Ledger Setup window (Financial Area Page under Setup).

2.  Select the check box for ‘Close to Divisional Account Segments’ and select segment 1.

3.  Verify that the DIVACTSG field has a value of 1 in the GL40000 table.

4.  Go back to the General Ledger Setup window and uncheck the box for ‘Close to Divisional Account Segments’.

Expected results:

The DIVACTSG field would be set to a 0 in the GL40000 table.

Actual results:

The DIVACTSG field will still contain the segment selected when you originally did the setup.


Run an update statement to set the DIVACTSG field to 0.

Basically, if you select the check box to close to Divisional Account Segments in the General Ledger Setup window and you specify a segment the DIVACTSG field in the GL40000 table gets updated. 

The issue is that when you unselect the option in the General Ledger Setup window, the DIVACTSG field does not get updated with a 0.  It holds the value that was initially selected, the window greys out but the value is still a 1.

This is a problem when generating the Year End Closing report for companies that are not using Multicurrency.  There are two different reports generated for the year end close-one if you are registered and using Multicurrency and the other is when you are not registered or using Multicurrency.  The problem is that during the year end close process, the code breaks the profit and loss accounts into multiple retained earnings lines.  Only the first RE line is written to the temp table because the rest are rejected due to key errors.  The key on the table is composed of the ACTINDX, Ledger_ID and CURNCYID fields.  The report pulls from this temp table and so it is only getting the first line that is inserted into the table.  The tables are correct, but the report is wrong. 


Remember I was able to reproduce all the steps many times while testing and trying to identify the issue because I had a pre close backup and a test environment to perform the close. Lucky me to always backup otherwise I would have been doomed by the following phrase “I have done millions of GL close procedures I don’t know what to do now….”


Have a great day!!!


How to remove the Connect section on the HomePage in Dynamics GP (via SQL)

Hello fellow readers, a few of us work in environments where we support many 282962_4217420760459_350721136_nusers or even better many instances of GP at a time.


Lately for many of our customers the Connect section has not been working as expected, for a few its just an ugly Error 404: File or directory not Found.

Error 1


Or simply it has been getting in the way of Integration Manager. Even when many colleagues like Mariano Gomez, Mark Polino, David Musgrave, Leslie Vail and others have provided help to the community on this issues. One question remained unanswered: How to massively remove it without the administrator login into each account?.

Famous engineer Tami Jones from the RoseASP team came up to my beach front office with a wonderful view of the Pacific ocean (ok it’s a 3 wall cubicle with no view) and asked me how can we automate this process as you know we team up with myGPcloud as well.


So I decided to take a look at the homepage behavior and came up with the following descriptions:


SectionID Value 1 is the ToDo Section
SectionID Value 2 is the Outlook Section
SectionID Value 3 is the Metrics Section
SectionID Value 4 is the My Reports Section
SectionID Value 5 is the Quick Links Section
SectionID Value 6 is the Connect Section


The table responsible for storing the values to display or hide those sections is SY08100 in Dynamics DB. So here is the script that will help you remove the connect section:


Code 1

With a little tweak you can limit the update statement to work only on 1 user or a set of users.

Always remember to backup your database and try statements in a test DB.


Have a great day!!!


GP 2010 Document E-mail Distribution

Dynamics GP 2010 includes new functionality that allows you to easily send sales and purchasing documents to your business partners via e-mail.  This is easy to set up and easy to use.

There are two system set up screens to go to:

Tools  >>  Setup  >>  Company  >>  E-mail Settings


Then go to either the Sales Series or Purchasing Series from this screen.  Select which documents you want to enable for e-mailing.  You should also add a message for each document type so that the subject and the body of your e-mail messages are filled out appropriately.

After the system setups, you can go to individual customer cards and set up e-mail settings and e-mail addresses.  Open the Address ID associated with the customers “Bill To” address.  Click on the Internet Address icon, next to the Address ID and add the e-mail addresses for the individuals to whom you want to send the documents.

Next  you’ll want to go to the  E-mail Options tab, from the Customer card to indicate which documents you want to be sent to the customer via e-mail.

Once you’ve set up your customers to receive documents via e-mail, you can send documents from a batch or from individual transactions.  To send from the Sales Transaction screen, click on the e-mail message icon at the top of the screen.

You can also send documents from the batch as well.

Here is a quick video I created to show this in action:

Lessons Learned: CRM 4.0 Adapter for GP 10.0

We are excited about this new integration with Microsoft CRM and Dynamics GP, so we jumped right in, and installed it on our production systems. Our systems engineer is a genius, and what follows are his comments related to his experience. Consider this an experiential addendum:

1. Must have GP 10 SP4 applied. (There are caveats to sp4 so be aware)

2. Must have at least rollup5 for CRM applied. (Rollup6 was out in Oct 2009 so I used rollup6)

3. Must have GPwebservices for GP10 sp4 and Management tools sp4 installed.

  • Had issues with the GPwebSerice not showing up in Dynamics Security console.
  • Ran a repair on the GPWebServices and the management tools and it resolved the issue.

4. Must have Microsoft Visual C++ 2005 Service Pack 1 Redistributable Package ATL Security Update applied to same server GPwebservices is installed.;=766a6af7-ec73-40ff-b072-9112bab119c2#filelist

5. Must have domain service accounts created. You can determine how many you need. Recommend at least 3 svc_gpweb, svc_gpcrmadmin, svc_gpcrm.

6. GPWebservice Account – Permissions granted during install.

7. Installation account – The account that installs the Microsoft Dynamics CRM Adapter for Microsoft Dynamics GP.

a. Be a domain user and a member of the Local Administrators group on the server where Microsoft Dynamics CRM Adapter for Microsoft Dynamics GP is to be installed.
b. Be assigned at least the dbcreator and securityadmin server roles on the SQL server where the MSDI database is to be stored

8. Integration account – The account that the Microsoft Dynamics CRM Adapter Service runs as. Permissions granted during install of Dynamics GP CRM adapter.

9. Configuration account – The account that runs the Microsoft Dynamics CRM Configuration Utility.

a. This account must be able to modify the membership of the CRM PrivUserGroup Security Group in the domain that the Microsoft Dynamics CRM Servers are joined to.
b. If this account is not in the db_datareader and db_datawriter roles for the Microsoft Dynamics CRM organization databases, you must use a SQL Server user name and password to update the Microsoft Dynamics CRM organization databases.

10. Integration GP account – The account that the Microsoft Dynamics CRM Adapter for Microsoft Dynamics GP uses to connect to Microsoft Dynamics GP Web Services.
a. Permissions granted in the Dynamics Security Console

11. Integration CRM account – The account that the Microsoft Dynamics CRM Adapter for Microsoft Dynamics GP uses to connect to Microsoft Dynamics CRM Web Services.
a. This account must belong to a Microsoft Dynamics CRM User that has organizational-level read privileges for system user on the CRM Organization that you plan to integrate.

12. CRM Deployment Administrator account – The account that the Microsoft Dynamics CRM Configuration Utility uses to retrieve a list of Microsoft Dynamics CRM organizations and apply customizations to the Microsoft Dynamics CRM organizations.

13. GP Adapter configuration – Had an error: application domain …… Only way to fix it was uninstall adapter and remove MSDI database. (just removing database may have fixed it but just uninstall did not)

DynamicsGP Version 10 on Windows 7?

Will DynamicsGP Version 10 run on Windows 7?
Why yes it will, thanks for asking.

Actually, all the pain we experienced with Vista has made this a non-event. There are even printer drivers that are labeled for use with Vista that work with Windows 7. Even the 64 bit version.

I’ve loaded GPV10 on 2 Windows 7 machines with success. The first install was on an old workhorse Dell Optiplx 760 with an Intel core duo and 1 GB RAM. This machine was loaded with Windows 7 Professional Edition. The machine was slow to launch GPV10, (as should be expected) but once the app was running, performance was pretty good.

The second machine was a new Optiplex 960 with 8GB RAM running 2 X 160 GB 10K RPM HDD in a RAID 1 array configuration. This machine is running Windows 7 Ultimate Edition 64 bit. Naturally this machine launches into GPV10 a little quicker.

The only issues I ran into was the dreaded ‘please verify your ODBC settings and that BCP has been correctly installed. Utilities will exit’. This is an easy fix and is caused by UAC (User Account Control) being turned on by default. Tech Support gave me this:

In Windows 7 the UAC functions a little differently as it is now a slider rather than a distinct on or off feature. Below are directions to locate and turn off this feature.Start Control Panel User Accounts User Accounts Change User Account Control SettingsWhen you open this you will see a slider, slide it to the bottom to turn it off. This should allow you to continue with your service pack installation.

The only other issue I ran into was with ODBC DSN on the Windows 7 Ultimate machine. Utilities wouldn’t recognize the DSN when it was configured on the ‘System DSN’ tab. No problem when configured on the ‘User DSN’. Permissions no doubt.

Net result. Fear not Windows 7. Go forward and dog-food this much improved OS.

It Pays to Know a SQL Expert, Especially if you are not!

As consultants, we all have aspects of our work that are more frustrating than others. Migration of data is one of those tasks that I prefer to do alone…I tend to swear a lot and it isn’t pretty! Last week I had a payroll migration that was one of the most hairy to date and I have been doing this for years.

It started out very vanilla. After all, it doesn’t matter the quantity of data…it is all about the quality, right? I only needed current year employee payroll transactions. My methodology is very tried and true. I Download the data into Excel, clean it up, save it, map it to Integration Manager and after a few hit and miss tries…the data has been successfully integrated. I can move on to something more exciting.

My client runs about 2,500 payroll checks a week and there is a massive amount of employee turnover. In the legacy system (which is housed in a separate facility from the new implementation) employees were not inactivated and, after several years, there were literally hundreds of thousands of employee master records.

As in Dynamics GP, the data that I needed was in several different tables, and had to be exported. No problem I think, and dump out the historical payroll transactions via SQL table export. I will do the same thing with the entire employee master record table, Do a VLook-up to parse out the employees that haven’t been paid this year…and there you go… I have what I need to import into my new GP database.

Who knew there was a limit to how much data Excel could handle. Yes, I know, there is a max. amount of rows – and Excel 2007 can hold a lot! Apparently, not enough for my employee master and state tax file. After several attempts (and a lot of swearing) I have the data in separate workbooks. Now to get rid of the employees I do not need. Not so easy. The calculation speed was painfully slow and while I could import data to all 1M rows, Excel couldn’t run the formula due to memory constraints (so I am told.) 12 hours later I am completely frustrated and at my witts end.

This is where the story gets good. My fairy godmother (a.k.a. my boss) tells me to quit pulling my hair out and call Tom Celvi. He is a SQL wizard! I am a bean-counter and application consultant. I have always thought of SQL as something to be respected and a bit feared. After all, it is the “house” for my GP data.

Silly me! There is a whole other world out there that I owe myself to learn more about. Tom has shown me that SQL is a very powerful tool and can be used for other things besides housing my precious GP databases.

Tom created a database within the clients SQL 2008 environment, imported all my raw, ugly data and through a series events that I can only explain as pure alchemy…he managed to move that cleaned up data into my GP database and I had my data in all the correct payroll tables along with only the 12,000 current employees!

This took Tom 10 hours in total and I bet he didn’t swear once! I was then able to successfully integrate the current year payroll transactions via Integration Manager and my my client is live and processing payroll.

Under lessons learned:

  1. SQL doesn’t have to be feared, it can be your friend (but should be respected)
  2. Quality over Quantity is over rated – strive for both because Excel does have its limits.
  3. hang out with a SQL guru, it will save on hair dye

Changing Purchase Accounts on PO Receipts

If you want to change the purchases account on a PO receiving transaction but don’t want to go back to each line item to change the account, you can add additional distribution lines with the Type = OTHER. You would enter a credit to the default PURCH account(s) and then enter a debit to the new/correct account(s). This feature can also be used if additional accounting entries related to the transaction are needed (to record use tax, for example). As long as you use the OTHER distribution type and have the total debits = total credits for the additional entries, there won’t be any distribution error messages.

Subscription Options:
Subscribe via RSS
Articles Categories