GP 10 SP 5 is out (with lots of AA Fixes)

GP 10 SP5 is out – See the post by David Musgrave for download links, which has some important notes as well -

Microsoft Dynamics GP 10.0 Service Pack 5

Worth pointing out specifically - “Once service pack 5 is installed, there is NO update path to GP 2010 until the release of service pack 1 for GP 2010.”

I have been awaiting this service pack for 20 odd Analytical Accounting Fixes. Somehow I had run into most of them in the last few months – they all had fairly easy workarounds, but were irritating. I was going to report one of them to Microsoft in a few hours today, till I saw the description in the Fix List.

See the complete Fix List for GP 10 SP 5 in the Service Pack 5 install guide  (10.0SP5_install_guide_enus.pdf) – Page 16 onwards.

Service Pack, Hotfix, and Compliance Update Patch Releases for Microsoft Dynamics GP 10.0 (PartnerSource)

Service Pack, Hotfix, and Compliance Update Patch Releases for Microsoft Dynamics GP 10.0 (CustomerSource)

Below are some of the important AA Fixes (See complete list in the document above)-

Fix You encounter a primary key error if you have installed Analytical Accounting in your system and you attempt to post a
Purchase Order Processing shipping documents batch created in another system in which Analytical Accounting is not
installed, and if zero aaSubLedgerHdrID records exists in the AA subledger table.
Fix The data received from the distribution query is not sorted correctly.
Fix Distributions are not updated in the AAG10001 table for a multicurrency transaction if you back out a journal entry and
change the transaction date.
Fix If you back out a journal entry in the Transaction Entry window, the SEQNUMBR created for the distribution in the AAG30001
table is different from the SEQNUMBR created for the distribution in the GL20000 table. (This was the issue I was going to report today!)
Fix If you have a Sales Order Processing batch with documents having document numbers that are not in sequence and you
post the batch in summary, the distributions are not created in the AAG10001 table.
Fix Running the Transfer Transaction Data to History utility for a historical year which is not the recent closed year updates
incorrect value in the HSTYEAR field of the GL 30000 table.
Fix Analytical Accounting information is missing from the AAG2000x tables for Purchase Order Processing transactions.
Fix Duplicate distributions are created in the AAG30001 table for an invoice created in the Purchase Order Processing module if
you have deleted and inserted a tax distribution for this invoice, and then you post and void this transaction.
Fix Transaction dimension codes assigned to the account class must be displayed automatically for transactions posted without
assigning a transaction dimension code.
Fix Analytical Accounting transactions are not validated when you perform a series or master posting.
Fix Duplicate distributions are created in AAG30001, AAG30002 and AAG30003 tables if you void an invoice saved in a recurring
batch that is posted previously.
Fix Balances are not carried forward for transactions posted to a closed year when you run the Transfer Transaction Data to
History utility.
Fix The balances are not brought forward for the combination of codes as entered during transaction entry.
Fix Records are incorrectly inserted in the GL20000 table if you have journal entries with the same journal entry number both in
the General Ledger history and open tables, and you run the Move Transaction Data to History utility.
Fix An SQL Server error occurs when you log on to Microsoft Dynamics GP and perform the Analytical Accounting year-end
close or if you generate reports.
Fix Incorrect Analytical Accounting assignments are displayed when you change the unit cost in the Purchasing Invoice Entry
window.
Fix A primary key error occurs in the Analytical Adjustment Entry window when you select a journal entry posted with allocation
accounts.
Fix Analytical Accounting information is not available for Bank Management transactions.
Fix Deleting line items in the Item Transaction Entry window does not remove distributions correctly from the AAG20001 table.
Fix Duplicate distributions are created in the AAG30001, AAG30002, and AAG30003 tables for Payables Management
transaction if you have deleted and inserted a tax distribution for an invoice.
Fix An error occurs when you attempt to post a Sales Order Processing batch if the batch name contains an apostrophe.
Fix An Analytical Accounting error message appears incorrectly when you attempt to post a NSF debit charge for a cash receipts
transaction, even if distribution accounts are not linked to an account class.
Fix Inactive transaction dimension codes are included when you assign codes to a budget tree.
Fix Analytical Accounting transactions are incorrectly validated in the Receivables Transaction Entry window if you select French
(Canada) as the regional option in your system settings.
Fix The originating amount is rounded off to the nearest integer if Multicurrency Management is not registered.
Fix The Analytical Accounting posting journal contains journal entries from a different batch if you post two General Ledger
batches simultaneously.
Fix The primary key error occurs when you run check links for Analytical Accounting.
Fix Clicking the Redisplay button in the Payables Transaction Entry Distribution window does not regenerate the sequence
number in Analytical Accounting.
Fix A SQL Server error occurs when you post a Sales Order Processing batch containing transactions originated in the Contract
Administration module.
Fix The Show valid code combinations in transactions and budgets option does not work for Bank Management transactions.
Fix The Analytical Accounting information is not correctly updated in the AAG10001 table for posted cash receipt transactions if
you have marked the Create a Journal Entry per Batch option in the Posting Setup window.
Fix Incorrect GLPOSTDT in the AAG40000 table for transactions posted to historical year.
Fix If you have installed the Project Accounting module on your system, Analytical Accounting information for Receivables
Management transactions posted from a batch are not updated in General Ledger.

Install SP4 if you are using Analytical Accounting in GP

Microsoft has fixed quite a few AA issues in SP4 for GP 10, including critical fixes with AA CheckLinks. As I mentioned earlier, many Duplicate record problems in AA have also been resolved. So please install SP4 and be good to yourself if you are using Analytical Accounting.

If you run Check Links on the Analytical Accounting logical group of tables but run into the following error messages:

"[Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL into column 'aaGLHdrID', table 'DBNAME.dbo.AAG30001', column does not allow nulls. INSERT fails."

and/or

"The stored procedure aagGenericGLOpenTOD returned the following results: DBMS 515, Microsoft Dynamics GP"

and/or

"[Microsoft][SQL Native Client][SQL Server]Violation of PRIMARY KEY constraint 'PKAAG30001'. Cannot insert duplicate key in object 'dbo.AAG30001'.[Microsoft][SQL Native Client][SQL Server]Violation of PRIMARY KEY constraint 'PKAAG30001'. Cannot insert duplicate"

There are Hot Topics on in PartnerSource and CustomerSource suggesting that users do not run Check Links on Analytical Accounting data until Service Pack 4 has been installed.

https://mbs.microsoft.com/partnersource/resources/support/selfsupport/hottopics/greatplains/htaa10a.htm?printpage=false

Specifically there are  the following issues with Check Links prior to SP4:

#6570 - "Slow performance when you run Check Links on the Analytical Accounting logical group of tables"

#6468 - "Error message when you run Check Links on Analytical Accounting if you have closed the year since installing Service Pack 2: "[Microsoft][SQL Native Client][SQL Server]Violation of PRIMARY KEY constraint 'PKAAG30002'"

#6423 - "Check Links on Analytical Accounting returns the following error message if you have journal entries posted in recurring batches where the transaction date was overriden: ""[Microsoft][SQL Native Client][SQL Server]Subquery returned more than 1 value"

#5622 - "Issue with AA Check Links"

AA Error : "Cannot insert duplicate key in object"

If you are using Analytical Accounting and get the error which is something like "[Microsoft] [SQL Native Client] [SQL Server] Violation of PRIMARY KEY constraint 'PKAAG20003.' Cannot insert duplicate key in Object 'AAG20003'."

The table name could be different.

Your first steps should be to check out KB article "897280" which has a script to fix this error. This should solve the problem 90% of the times.

However,  if you still get the error, this means the numbers are correct in the AAG00102 table and a duplicate key error is often caused by an invalid record in the AAG2000X  series of tables.

Run the following script against your company database -

select * from AAG20000 where aaSubLedgerHdrId = 0

select * from AAG20001 where aaSubLedgerHdrId = 0

select * from AAG20002 where aaSubLedgerHdrId = 0

select * from AAG20003 where aaSubLedgerHdrId = 0

If you get any results - those are the entries causing the problem. The 0 in the aaSubLedgerHdrId is not a valid record since the next number needs to come from the AAG00102 table. 

Hope that helps!

AA Table Changes with GP 10 SP2

If you are using GP 10 SP2, FRx Cannot Report on Analytical Accounting History Following an Year End Close.

With Microsoft Dynamics GP 10.0 Service Pack 2 - Microsoft has made some Database changes . When the year end close process is run - it automatically moves the AA transactions from the AAG30000 tables to the AAG40000 series tables.

FRx does not read from the AAG40000 series of tables and therefore, cannot report on historical data for Analytical Accounting. You get the error - "This report yields no data".

There is a hotfix available from Microsoft (hotfix 962862), but you need to contact Microsoft for it, its not available for public download yet.

I have not done any tests yet, but this should lead to performance improvements in your AA Reports. This would also mean that you need to update your custom AA Reports to read data from AAG40000 series tables.

Unhandled Object Exception While Importing a Budget from Excel

While trying to import a budget from Excel in GP 10 we got this error -

Unhandled Object Exception

Unable to access field ‘Workbooks’

Exception Class Object Exception

Followed by the message –

You must have excel installed on this machine in order to run this application.

Excel was definitely installed. The excel sheet was closed. I looked around in newsgroups and knowledgebase but did not find anything useful. Our in house GP expert Rubal told me to look into the task manager to check if there was an instance of Excel in there. Sure it was. I ended that instance and the error went away.

Surprisingly I have seen this error twice in two weeks with two different customers, so I thought of putting it out here in case one of you found it useful.

The original Error

Followed by the message - You must have excel installed on this machine in order to run this application.

End the Extra Excel Process in Task Manager

Exporting Currency from SmartList to Excel

We received a simple request for a smart user - who was having trouble when Excel sheets were exported from SmartList. It exported the currency along with the values, and it would take him time to clean it up to do totals.

There is an old setting in in dex.ini ExplorerFormatCurrency, which controls if currency amounts are exported as text or numbers.

Making ExplorerFormatCurrency=FALSE ensures that foreign currency is exported as easy to use numbers.

I am not sure if this is still supported, but it is definitely pretty useful.

Yesterday, I found that if you use ExplorerFormatCurrency=FALSE, Analytical Accounting SmartList's export 0 amounts to excel. It shows data in the SmartList, but exports all zeroes to Excel. Turning off the ExplorerFormatCurrency fixes this problem.

Excel Being Mr Smartass - Episode #599

A user trying to import a Budget into Dynamics GP kept getting an error - “The number of periods for the budget does not match the number of columns in Excel. Import will not be able to complete.”

Being pretty smart - he tried exporting out a blank template from GP and then importing it back in, and still got the error. He compared all the columns one by one and they all seemed same. He tried deleting columns, using different version of Excel ... but could not get rid of the error.

So we started researching and found that their Fiscal Periods were named - "January", February", "March" ...

On export, excel automatically converted the columns to DATE format each time. GP, however, was looking for TEXT format.

The resolution from Microsoft to fix this is -

1. In the Excel worksheet, highlight all the column headers for the periods.

2. For Office 2003: On the Format menu, click Cells.

For Office 2007: Right click on cell and select Format cell..

3. On the Number tab, click Text in the Category list.

4. Click OK.

5. If the cells are changed to numbers, re-enter the period headers to match the fiscal period setup.

I do appreciate GP support identifying the problem and offering a solution. I can understand why GP has a problem, and I can understand why 90% of the users would not figure this out themselves. This is just excel being Mr Smartass. All over again.

"RPC Server is unavailable" when you run an Integration

If you get this error when running an integration -

1. Exit Integration Manager and GP 10

2. Start GP 10

3. Start IM 10

4. Run the Integration again.

Thought it might help somebody!

IM 10 : Error Message - Integration Destination not set

You build and test an integration. You test it again. Then you go to a user to let them test it. "Okay all you have to do is open this integration and Click "RUN""

And then you get this error.

"This integration cannot be run because of the following problem(s):

Integration Destination not set. Use Add Destination to set a destination object for the integration. The destination specified by this integration uses an adapter that is not installed.

The destination will be removed from this integration."

Cause #1 : You were over confident.

Resolution #1 : Never look confident when using Integration Manager . Reduce your confidence and try again.

Cause #2 : You are pointing to a shared Integration Manager Database and somebody else has this integration open

Resolution #2 : Close Other open instance of Integration Manager and try again.

GP 10 Security : Power User is more Powerful than all other roles combined!

Recently we got a request to remove access to one window in GP for a user, who was otherwise a "Power User".

We had to remove access to the 'Challenging' Posting Setup Window, which I would write about in detail sometimes soon. Depending upon your experience with the Posting Setup Window you can substitute 'challenging' with words of your choice.

So we created a new role, selected all tasks in it, just removed access to posting setup window. We then changed the role of the user from Power User to this new role.

A week later, the user started reporting problems. It turned out even with all tasks assigned to the user, they didn't have access to certain areas. This was because - there were no tasks for those areas. I had written about how there are no inbuilt tasks Safe Pay Earlier.

So we ended up creating new tasks for all these areas. This was an iterative process, where we created tasks for various products. I will try and document all these areas in the future for your reference. It is a lengthy process to create tasks for all these missing modules in an organized way.

Meanwhile, Mr Power Users can keep their legacy for a while longer.

GP Installation/Update Error : An installation package for the product Microsoft Dynamics GP 10.0 cannot be found. Try the installation again using a valid copy of the installation package 'GreatPlains.msi'

You run Great Plains setup to install/update a feature and get the error message - "An installation package for the product Microsoft Dynamics GP 10.0 cannot be found. Try the installation again using a valid copy of the installation package 'GreatPlains.msi'"

I have seen this a few times so here is the fix -

You generally get this error, if you installed Great Plains from a location say - C:\GreatPlains\CD1\ and now you are updating it from a location like - D:\greatplains\Cd1\.

Option 1 :

This has worked for me most of the times -

1. Backup your registry(If you dont know how to do this, you should skip to Option 2)

2. Search For GreatPlains.msi

3. There may be several entries so you need to find the one which lists a LastUsedSource entry. This is the entry you will need to edit. The entry will look something like this.

m;1;C:\Great Plains Installers\Dynamics GP 10\CD1\Bin

You need to change the path name so it is the location of the GreatPlains.msi file on the install CD.

4. After you edit the registry you can close regedit.

Try running the installation again.

Option 2 :

You can un-install and then re-install Dynamics GP. This will correct the registry setting that stores the path to GreatPlains.msi. Your accounting Data should not be erased by uninstall as that's stored in SQL.

GP 10 does not have an inbuilt role/task for Safe Pay

Today while giving a user access to Safe Pay (Tools > Routines > Financial > Safe Pay), I realized that there wasn't an inbuilt task/role for Safe Pay. I was a little surprised, as this was the first major missing task in the otherwise excellent default Security Role /Task Setup.

However, it wasn't too difficult to set it up, here's what you have to do -

1. Create a new task called SAFE_PAY (or whatever, you want to name it according to your security setup)

2. Select Safe Pay as Third Party Product and give access to the Safe Pay Windows and Reports (Let me know if you need details on this)

3. Assign this tasks to a Role

4. Give the user who needs safe pay access, access to this role.

You should be able to see Safe Pay Menu option after this.

Installing Great Plains Version 10 on .Net Framework 2.0 SP1

UPDATE : There is a transform file now available to fix this issue Error message when you try to install Microsoft Dynamics GP 10.0: "Product: Dexterity Shared Components -- .NET Framework 2.0 is not installed. The installation cannot continue"

UPDATE 2 : The simplest solution - If your IT Manager allows is to just uninstall .Net 2.0 SP1 and run setup again. This will install .Net 2.0 again.

UPDATE 3 : AUGUST 2008 - The latest GP 10 Feature Pack 1 DVD installer, does not have this issue. So that the one I have started using.

I read in the newsgroups about this and didn't believe it, till I ran into the issue today.

If you try and install GP 10 on a machine that has .Net 2.0 SP1, the installer would fail. It installs just fine on machines that have .Net 2.0.

Patrick from MSFT Developer support wrote -

I actually ran the install of GP 10.0 and sometime during it I get the error as well. So something in the install yet again is checking incorrectly. I don't see how development is going to resolve this unless they rebuild the installs for 10.0 RTM and 10.0 SP1.

I ended up uninstalling .Net framework 2.0 SP1 from the machine and then installing .Net 2.0. I had this luxury with the machine being a new machine, but this would be impossible in most cases. What complicates matters is the fact - that .Net 3.0 requires .Net 2.0 SP1 to be installed. And you can't uninstall .Net 2.0 SP1 if you don't uninstall .Net 3.0. Phew!

There is no knowledgebase article or hotfix about this yet.

However, I just read that if you open a support case with Microsoft - " There is a transform file you can use to install both the Dexterity Shared Components and another one to install Dynamics with. Because this is an issue with our install, the case shouldn't be chargeable unless you have other issues outside this scope. "

I am very interested in knowing how Microsoft resolves this in the long run. Would they release a new setup file ? Will they send out this information to partners ?

I kind of sympathize with them, because I know it can be a pain testing against all versions of frameworks. Windows update would install .Net 2.0 SP1 on most machines, so this probably something we need to be aware of.

GP 10 on Windows XP/SQL Express (Don't Try this at Home)

We installed GP 10 Server on a Windows XP machine recently. I don't recommend this as Windows XP is not supported as a server. However, a client didn't want to upgrade his server which was on GP 7 , and well ... the client is *always* right!

Everything seems to be working reasonably well till now.

Earlier, during our testing, we faced an interesting issue - we could not add any users.

It was one of those issues where everybody got interested, because this was working perfectly well in our test environment. We got together to troubleshoot it.

When we tried to add a user from within GP we got the helpful message below.

"The Creation of the login failed for an unknown reason. Contact your SQL server Administrator for assistance"

When as an administrator you get a message asking you to contact an administrator, it is not very comforting.

Tina suggested that we make a user in SQL and see if that works. When we tried to add a user in SQL we got the error below -

"The MUST_CHANGE option is not supported by this version of Microsoft Windows".

At this point Rubal put it together and suggested we turn off the Must Change password option. We did that and were able to add users.

We did the same thing from within GP - turned off the option to change password and we were able to add users.

Turns out, Windows XP and SQL Express combination doesn't allow creation of users with the password policy on. Hope that helps someone.

GP 10 Gotcha : Send to PDF with Outlook 2003 doesn't work

One of the issues I had seen after an upgrade to GP 10 was that "Send to PDF" with Outlook 2003 had stopped working. Microsoft Tech Support responded saying this was an identified issue which would be resolved in SP1. SP1 is due in November.

I saw somebody else facing this issue in the newsgroups, so decided to post the options I knew of here.

So here are your options in case you have upgraded and are facing this issue

1. Upgrade Office 2003 to Office 2007. This issue exists only with Office 2003. We ended up taking this approach!

2. Wait for SP1.

3. Use a work around to print a PDF and attach it manually.

a. Open the print setup window and change the printer name to “Adobe PDF”

b. Click print on any report, a PDF would be generated. You can save this wherever you want.

c. Attach the file manually to a new email.

Use the Print Setup Window to set Adobe PDF as the printer When you Click Print a PDF would be generated. You can save and attach this to an email

Troubleshooting Long Login Times and Performance Issues in Great Plains

We faced this problem specifically for GP 9.  We tried a host of steps, then created a support ticket, submitted information log and trace info to Microsoft, and what actually fixed it was - moving to the latest service pack.

I'll put the information here - in case somebody finds it useful.

1 Check that you are running the latest service pack for Great Plains AND MSSQL.

I was used to ignoring this, and "Apply the latest windows updates" suggestion. But sometimes, this is the only thing that fixes things.

2 Check if tracing is turned on for all ODBC's

If tracing is enabled - there will be a sql log file created that would record every ODBC call that is made to any application that uses the ODBC drivers. Therefore the application is slowed.

To check the setting, click Start | Settings | Control Panel | 32bit ODBC | Select Tracing tab.

3 Check if a Dexsql log is accidentally on

Open the Dex.ini file that exists in your Great Plains application folder in Windows Explorer. Find and confirm the following statements in the Dex.ini file:

SQLLogSQLStmt=FALSE

SQLLogODBCMessages=FALSE

SQLLogAllODBCMessages=FALSE

4 Turn off Home Page Display and check login time

Specifically the metrics and outlook section can be slow

5 Turn off security from Reminders and Check login time

Dynamics GP >> Forms >> System >> Reminder

6 Run profiler and review queries fired during startup

7 Are any anti-virus softwares installed on the system?

If the issue replicated only on machines that have the anti-virus

8 Can you reproduce the performance issue on all the computers?

This would identify if this is a machine related problem

9 Specifically, can you reproduce the performance issue when you are sitting directly at the computer that is running Microsoft SQL Server?

This would identify if you have network problems

10 Are there any physical symptoms on the computer that is running SQL Server? For example, is processor usage at 100 percent? Is the processor light on? With How many users do you face this problem ?