Online Services for Microsoft Dynamics GP, what's the point?

Just in time for the weekend...

My new IMHO with The Dynamics GP Blogster column is out. This time, I tackle the new Online Payment Services module, made recently available with Microsoft Dynamics GP 10.0 SP5 and Microsoft Dynamics GP 2010 SP1 -- and got direct input from my buddies over at Microsoft.

If you want to know more about Online Payment Services and the reason why it was created take a look at the article.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

Read More

Microsoft SQL Server performance boosting settings for Microsoft Dynamics GP - Part 1

Part 1 of 2 - Microsoft SQL Server performance boosting settings for Microsoft Dynamics GP.

---

Disclaimer: this is not a "one-size-fits-all" recipe for improving Microsoft SQL Server performance and you will hear me say numerous times that you must consult with your Microsoft SQL Server administrator to understand how these settings may (or may not) apply to your environment.

In addition, I have recommended (and implemented) some of these changes in different customer environments on a case by case basis, but have never had to make them all at the same time, so please don't take this as a "Mariano said I must make all these changes to have my Microsoft Dynamics GP SQL Server fly" message. To the contrary, this post is intended to present some settings that may contribute to improve SQL Server performance, based on your specific environment. So here is my list:

Minimum memory per query
Increasing the value of this option can improve the performance of queries that use hashing or sorting operations, like those find in SmartList and SmartList Builder. Also, most table auto generated stored procedures -- the stored procedures prefixed with zDP, see David Musgrave's article, What do the zDP_ Auto Generated Stored Procedures do?, for more information-- will benefit from this change if your SQL Server has a lot of memory available and there are many queries running concurrently on the server.


Server Properties | Memory



SQL Server will automatically allocate, at a minimum, the amount of memory set in this configuration setting. The default value for this option is 1024 KB.

Network Packet Size
Use the network packet size option to set the packet size (in bytes) used across the entire network. Packets are the fixed-size chunks of data that transfer requests and results between clients and servers. If an application does bulk copy operations, or sends or receives large amounts of text data, a packet size larger than the default may improve efficiency because it results in fewer network reads and writes. If an application sends and receives small amounts of information, you can set the packet size to 512 bytes, which is sufficient for most data transfers.


Server Properties | Network Packet Size


The default packet size set by Microsoft SQL Server is 4096 bytes. Microsoft does not recommend changing the packet size unless you are certain that it will improve performance.

Default Index Fill Factor
The Default Index Fill Factor option specifies how full each index page will become. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page.

Server Properties | Database Settings

The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty. The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created. If the table contains read-only data (or data that very rarely changed, for example like Master tables), you can set the 'fill factor' option to 100. When the table's data modified very often, for example like MRP tables or SOP and GL transaction tables, you can decrease the 'fill factor' option to 70 percent, for example.

Recovery Interval
The 'Recovery Interval' option specifies the maximum number of minutes per database that SQL Server needs to complete its recovery procedures. The default value of this option is 0. It means that SQL Server will automatically configure this option. SQL Server issues a checkpoint using the 'Recovery Interval' option. Microsoft does not recommend changing this option in general case, but sometimes you can improve performance by changing this option. You can monitor disk-write activity on the data files, and if you see periodic spikes that send disk utilization to 100 percent, you can increase the recovery interval. In this case, Microsoft suggests setting the 'Recovery Interval' option to 5 and continuing monitoring.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

Read More

Why shouldn't I shrink my Microsoft Dynamics GP databases?

A client recently approached me with the question of whether they should shrink their Microsoft Dynamics GP databases to reclaim hard disk space, but instead of telling you what I think, I will demonstrate some of the issues arising from shrinking your databases:

Now to what I think...I have never been a big fan of shrinking databases to reclaim hard disk space -- though, if you are running a dev environment where space is critical, then this may only be the one time. The problem arises from the way the shrink process occurs, and applies to DBCC SHRINKFILE, DBCC SHRINKDATABASE and the Auto Shrink setting in the database properties.

In summary, SQL Server goes to the end of a dabatabase file, picks up each individual page, then moves them to the first available empty space in the file. This process may reverse the order of your pages, turning perfectly defragmented indexes into perfectly fragmented ones.

So, let's take a look with a test database in one of my client's environments:

1. The first thing we will do is take a look at the stats on the GL00100 table by running the Microsoft SQL Server sys.dm_db_index_physical_stats function:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'TWO'), OBJECT_ID(N'dbo.GL00100'), NULL, NULL , 'LIMITED');

The following are the results from those stats:

Original database stats

 Now, in particular, I have highlighted the Average Fragmentation in Percent and the Average Fragment Size in Pages columns. Also, note that I have executed the function in 'LIMITED' mode, which combines information from all the columns that form part of each index. So, while these fragmentation levels would indicate low defragmentation rates -- which is always desirable -- it means there is still room for improvement. So, let's see what happens after rebuilding the indexes on GL00100 for our test database, then rerunning the stats:

Stats after rebuilding indexes

As you can tell now, we have no fragmentation and our page size utilization went up - this is what we would expect after rebuilding indexes. So let's see what happens when we run the shrink process on the database:

Stats after DBCC SHRINKDATABASE

You can now tell that perfectly defragmented indexes now appear fragmented and even to a higher degree than what we started out with. These levels of fragmentation can cause serious performance issues in a production envrionment where database maintenance procedures are not properly planned and executed.

If you must reclaim hard disk space in your Microsoft Dynamics GP environment, please consult with your database administrator, but also keep in mind that storage is dirt cheap.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

Read More

Maximum Global Business merges with IntellPartners, a Georgia based company

Dear reader,

The objective of this site has always been -- and will continue to be -- providing quality Microsoft Dynamics GP content you have come to rely on. Consequently, it's been customary not to mix my personal affairs with what you read on this site. However, and in response to the numerous emails and calls I have received over the past week, some asking about the change in my signature, others congratulating the move, I feel the time has come to explain the change.

As you know, I was the founder and managing director of Maximum Global Business, a Microsoft partner providing software licensing and implementation services to the Microsoft Dynamics ecosystem of partners and customers. This effort was a first in a list of entrepreneurial moves oriented to better service the needs of the community. Now, the time has come to move to the next level...

Maximum Global Business has merged with IntellPartners, an Atlanta, Georgia, USA based company with global market reach, to deliver strategic management and technology consulting services oriented to maximize the value proposition and go beyond the transactional nature of ERP implementations.

Get the full scoop over at MSDynamicsWorld:

Veteran Microsoft Dynamics Consultants Unite in New Push to Focus ERP Customers Beyond the Software Transaction

You will also be pleased to know that I have joined forces with fellow MVP Frank Hamelly in this new endeavor. You can read Frank's stub below:

East Coast Dynamics merges with IntellPartners of Atlanta, GA USA

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

Read More

Why my template changes don't save when I close Microsoft Word?

If you are now a user of Microsoft Dynamics GP 2010, you will concur that the new Word Templates feature is probably one of the most visible improvements over version 10.0 as it has made the delivery of 'facing' documents a pleasant one.

However, the new feature has not come without misunderstandings on how it works. One such misunderstandings has to do with the way template documents are opened, changed, and saved. Many users have found out the hard way that there's a bit more to just hitting the save button in Microsoft Word. So, let's review the few critical steps of opening a Word Template and ensuring that all changes are properly saved in Microsoft Dynamics GP, in the process, I will explain what happens "under the hood".

1. In the following example, I have taken a new template created for Aaron Fitz Electrical (AARONFIT0001) based on the original SOP Blank Invoice Form and have opened it and added a logo to it.

SOP Blank Invoice Form Template - AARONFIT0001

Upon opening the template (by clicking the Modify button), Microsoft Word automatically creates an image of the template in the user's local temp folder.

Template image in %temp% folder

In this scenario, and to simulate the subject of this post, we will proceed and save the template -- assuming the logo is all we wanted to add to the template. Microsoft Word will save the document as it would normally do with any other document. We then proceed to close Microsoft Word.

Upon revisiting the %temp% folder. The modified template is no longer available.

Template is no longer available after closing Microsoft Word

Hence, if you were to print the SOP Blank Invoice Form -- assigned to customer Aaron Fitz Electrical -- from within Microsoft Dynamics GP using the Template report, chances are the system would revert to the standard template as the changes would not be found.

In order to overcome this issue, here are the correct steps to have your templates available all the time:

1. Upon completing the modifications of the template in Microsoft Word, save your templates under your My Documents folder. If your templates are going to be used by all users in your environment, save the template to a network shared folder.  In any case, use the Save As option in Microsoft Word to save your document.

Save As to save template document

NOTE: if you are also going to use logo images with your templates, be sure to save these also on a shared folder.

2. Upon saving the template, you will now use the Add button to re-add the template you have just previously saved. This will now ensure that all your changes are imported and stored in Microsoft SQL Server.

Add template

That's it for avoiding a major headache with the loss of a very customized Microsoft Word template.
Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

Read More

From the newsgroups: Changing item currency decimal places

Every once in a while as consultants, we run into these requests that leave us scrambling for an answer. It is not at all strange to find companies that want to modify the currency decimal places supported only at the product level (item master), while wanting to maintain the currency decimals set from an accounting perpective (general ledger and financial reporting). For example, for product transaction purposes, some companies may require 3 or 4 decimal places, but for financial reporting and general ledger would still want to maintain 2 decimals. Here is what the consultant requested:

Is there a way to change "Currency Decimals" on the Item Maintenance window after an item is saved? If the field is not accessible which field in which SQL table has to be changed?

The answer comes courtesy of Microsoft's Tirumal Boppana from the Dynamics GP Online Partner Technical community forum. Tirumal outlines two methods that allow you to change your currency decimals and explains the values stored in the tables representing the on-screen decimal places.

There are actually two ways in changing the currency decimal places of the item.

Note: Secure a current restorable backup of the company database. You also have the option to restore your backup to a test company database so you can go through these steps in the test environment. I have attached KB Article 871973 - How to set up a test company that has a copy of live company data by using SQL Server 7.0, SQL Server 2000, or SQL Server 2005.

Option 1: Change the currency decimal place using the Change Decimal Places Utility. (Recommended)

1. Delete all unposted transactions against the item number from all module such as IV, SOP, POP, Manufacturing, Bill of Materials and Field Service. These transactions could be allocation of the item, stock count and anything that updates Inventory tables.
2. Use the utility to change the decimal place.

a. Go to Change Decimal Places window (Microsoft Dynamics GP menu - Utilities - Inventory - Change Decimal Places).
b. Mark Change Currency Decimal Place option and then click Yes when you get the prompt saying that 'Changing the currency decimals will round the amounts in the price list for each item included in the range. Do you want to continue?'
c. Select the decimal place you wish to change it to.
d. Select the appropriate Currency ID.
e. Click on Process.

Option 2: How to change currency decimals without having to remove items from Work transactions and without having to change price lists.

Here are the lists the steps that we can take to change currency decimals for items with a high turnover rate and cannot be removed from Work transactions. If the users would like to keep the price list of the item then this set of steps will also help.

Note: Do not use these steps if you are reducing decimal places and if the decimals you would like to lose have already been used. Doing this may cause data integrity issues for you on the costing of your items.

For example: Let's say that some of my cost layers for my A item have a cost tag that takes all its currency decimal places (like $1.12345 for 5 decimals, $1.1234 for 4 decimals and $1.123 for 3 decimals). If I force a change of the currency decimals to 2 for my item, then these cost values will be truncated (to show $1.12) and may cause issues with the cost values being posted to the General Ledger. This goes with the item prices that you have setup as well.

1. Make a complete backup of the company database. You can restore this backup to a test database if you would like to test these steps in a test environment first.

2. In SQL Server Management Studio, run the script below:

-- This script is provided "AS IS"
SELECT * FROM sysobjects o, syscolumns c
WHERE o.id = c.id AND o.type = 'U' AND c.name = 'DECPLCUR'
ORDER BY o.name

** This script will search the tables that have the DECPLCUR as a column name.


3. Run the script below only if the tables that were listed by the script in step 1 has an ITEMNMBR field.

-- This script is provided "AS IS"
Update TABLENAME set DECPLCUR = '0' where ITEMNMBR = 'xxx'

Note #1 : Replace xxx with the item number of the item we need to change decimals for.


Note #2 : Replace Y with your preferred currency decimal place field. Below are the possible integer values of the Currency Decimal Place (DECPLCUR) field in SQL Query Analyzer:


1 - 0 decimal place
2 - 1 decimal place
3 - 2 decimal places
4 - 3 decimal places
5 - 4 decimal places
6 - 5 decimal places


Example: Since I wanted to have 5 Decimal Places, the Update statement is: Update IV10001 set DECPLCUR = '6' where ITEMNMBR = 'CURRENCY'


Note: This option would not affect posted transactions. This change would affect the transactions that are to be posted.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

Read More

Troubleshooting printing issues with Microsoft Dynamics GP

Just lately, I have seen a number of inquiries over the various forums on printer support for Microsoft Dynamics GP (usually, versions 10.0 and 2010). If you have been in the channel as an ISV, partner, or customer since the days of the former Great Plains Software, you probably still remember that in the past testing of Microsoft Dynamics GP included exhaustive testing of a number of printers for compatibility.

In fact, leading up to the release of version 10.0, it was not uncommon to see the now infamous Printer Compatibility List, which would outline an extensive list of printers tested for all sort of "maneuvers" including large print jobs, paper handling, character blurriness, and so on. There are still some reminiscent KB articles from back in the days when printers were tested - go grab them now before they disappear!

KB article 870301 - Compatibility of Dynamics With the HP LaserJet 6L or HP LaserJet 6L SE Printer
KB article 865797 - HP OfficeJet 520 Printers
KB article 865796 - HP 6LXI Printers
KB article 865782 - HP Laserjet 5000, 5M and checks
KB article 865399 - Okidata ML 320 printing checks

Starting with version 10.0, Microsoft decided that "enough is enough!", well, not exactly in those terms, but the point being that printer manufacturers had sorted out most of the issues revolving around standards that plagued the 90's and 00's. Printer compatibility was mostly an issue during the transition from impact printers to laser printers -- for those of you out there too young to remember, this is what a dot matrix or impact printer looked like. This one in particular, happens to be an Epson (and yes, I owned one myself!).

Epson dot matrix printer

Another main reason was, they were simply too many printers to be tested! You can find the official word from Microsoft on printer testing with Microsoft Dynamics GP by using the following links:

Printer compatibility for Microsoft Dynamics GP 10.0 - click here
Printer Compatibility for Microsoft Dynamics GP 2010 - click here

Then what are you supposed to do if you suspect a compatibility issue between Microsoft Dynamics GP and a printer in your organization? Here is my list of suggestions/questions you should ask yourself to fight the printing battle:

1. Are you in a Terminal Server environment? Terminal Server environments are notoriously known for printing issues when printer driver versions on workstations do not match the Terminal Server's (or viceversa) during the printer re-direction process. I strongly suggest you consider downloading the Terminal Server Printer Redirection Wizard Tool, which will help you troubleshoot and replace print drivers that were unsuccessfully redirected.

2. Are you using Named Printers? There are a couple known issues with Named Printers "acting up" and not saving certain settings, thus causing erratic behaviors when printing. However, if you are using Named Printers on a Terminal Server environment, my good friend, David Musgrave has written what I consider to be the ultimate guide to Using Named Printers with Terminal Server.

3. Are all the printer driver versions the same on every machine? Different printer driver versions, even for the same printer, can produce unexpected behaviors, for example, if bugs have been fixed in the latest version of the driver. So always ensure you are running the same printer driver version on all workstations - oh, and make sure you obtain the drivers directly from the manufacturer's website, not the one shipped to Microsoft by the manufacturer. Many times, printer drivers are updated by the manufacturer after a version of Windows have been released.

4. Other common troubleshooting guidelines...does the problem happen to all users or one user in particular? You know you will be asked this question if you contact Microsoft Support, so do your homework and save yourself some time. This will allow you to establish whether there's a method to the madness you are experiencing. By asking this question, Microsoft Support will attempt to establish whether there's an issue with that particular user profile in your environment.

5. Does the problem happen with one machine in particular or does it happen across all machines? The objective here is to establish whether the behavior is consistent at the machine level, regardless of the user signed into the machine. If the problem can be replicated regardless of the user, then you can begin to establish a pattern. Working your way through several computers will help establish if there's a bigger issue at play.

For additional information on troubleshooting printing issues:

KB article 959033 - Frequently asked questions about printer issues in Microsoft Dynamics GP 2010, Microsoft Dynamics GP 10.0 or in Microsoft Dynamics GP 9.0

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

Read More