Why does Dynamics GP use Table Heaps (no clustered indexes)?

First we should define what a table heap is. A heap is a table without a clustered index.

Data is stored in the heap without specifying an order. Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently; so the data order cannot be predicted.


There are sometimes good reasons to leave a table as a heap instead of creating a clustered index, but using heaps effectively is an advanced skill. Most tables should have a carefully chosen clustered index unless a good reason exists for leaving the table as a heap.


Generally I would say we are more used to finding a clustered index on a table, but looking below you can see the more normal picture for the indexes of a GP table.


Microsoft Dynamics GP does not use many clustered indexes in its database.


If you read The Microsoft Dynamics® GP Architecture White Paper, then it would seem to be a decision based on research and data:


So which tables? Lets look for tables in the SOP series that have clustered indexes on them:

     t.name AS table_name,
     I.type_desc AS index_type,
     I.is_unique AS is_unique_index
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
     ON t.schema_id = s.schema_id
INNER JOIN sys.indexes AS I
     ON t.object_id = I.object_id
WHERE I.type_desc = 'CLUSTERED'
and t.name like 'Sop%'
order by 1;



Interesting is that only seven of the SOP tables that have clustered indexes, the rest are heaps. So large tables such as SOP30300, that is particularly large, a 8GB table in one of the companies I work with, are a heap. This at first glance seems wrong. in fact the MS advice from Heaps (Tables without Clustered Indexes) is

Do not use a heap when there are no non-clustered indexes and the table is large. In a heap, all rows of the heap must be read to find any row.


I do wonder about the conditions under which the decisions to not use clustered indexes was made, but have to trust the research was accurate (/S).


So the reason? GP didn’t always run on SQL server, it was on ISAM in the old days. The database schema that GP uses owes a lot to that legacy and is the reason for many of the oddities and areas lacking in GP’s use of SQL server and I expect this use of heap tables is also from that legacy and that study showed there were no performance benefits from introducing a clustered index.


There reports of people converting the primary keys into clustered indexes with success. It is possible to write a DML script to go through the database doing this for all tables too. However this is internet forum hearsay, so I cannot vouch for the accuracy of this information nor rigour of the testing. I also fear that the work can be undone when tables are dropped during upgrades, loosing the changes. I'm pretty certain this would also invalidate your support with MS, as all that careful performance tuning that GP database schema has experienced over the years may not be compatible with this action.  ;)

Finding a rogue “dll” causing trouble with “different version of the same dependent assembly”

In Dynamics GP development, we have lots of .dll files around arising from support for many version releases of GP. These files litter our projects and sometimes a dll may go astray and cause trouble by ending up in a folder to which it should not belong.

This powershell command is a quick way to look for all the versions of a .NET assembly (dll) version within a folder tree.

Get-ChildItem -Filter Microsoft.Dexterity.Bridge.dll -Recurse | Select-Object -ExpandProperty VersionInfo | Out-String -Width 180


ref: Stackoverflow Get file version and assembly version of DLL files in the current directory and all sub directories

The versions can be seen on the left and any offending .dll files that are not in the correct directory for their actual version number can be quickly and easily identified.

This avoids getting into <assemblyBinding> redirects when dealing with the error at compile time of

Found conflicts between different versions of the same dependent assembly

Excel changing link paths when opened from UNC vs Mapped Drive

Behaviour of links in excel is described in this good document.

Description of link management and storage in Excel

Looking at an excel cell when document opened via UNC path vs Looking at excel cell when document opened via mapped drive path:


The actual UNC path should be as below, the “files” folder has been left out by Excel, thus the links are broken when the Excel file is opened via UNC vs Mapped Drive.


For preservation of content, here is the relevant parts, but check the link for latest version:

How link paths are handled when a file opens
When Excel opens a file that contains links (linked workbook), it combines the portions of the links stored in the file with the necessary portions of the current path of the linked workbook.
How link paths are stored
When Excel stores the path to a linked file, it uses the following rules to determine what to store.
Note Moving up a path indicates that you are referring to folders moving away from the root drive or share. Moving down a path indicates that you are moving closer to the root drive or share.
  • If the linked file and the source data file are not on the same drive, the drive letter is stored with a path to the file and file name.
  • If the linked file and the source data file are in the same folder, only the file name is stored.
  • If the source data file is located in a folder that is nested in the same root folder as the linked file, a property is stored to indicate the root folder. All portions of the path that are shared are not stored.
    For example, if the linked file C:\Mydir\Linked.xls is dependent on C:\Mydir\Files\Source.xls, the only portion of the path that is stored is \Files\Source.xls.
  • If the source data file is one folder down from the linked file, a property is stored to indicate this.
    For example, the linked file is C:\Mydir\Files\Myfile\Linked.xls and the source data file is C:\Mydir\Files\Source.xls. Excel stores only \MyDir\Files\ .. \Source.xls.
    Note This allows a link to be maintained when the linked file is copied to an additional sub folder of the folder that the source file is located in.
    For example, the linked file is C:\Mydir\Files\Myfiles1\Linked.xls and the source data file is C:\Mydir\Files\Source.xls, the linked file, Linked.xls, is copied from the folder C:\Mydir\Files\Myfiles1 to a folder named C:\Mydir\Files\Myfiles2, and the link to C:\Mydir\Files\Source.xls is maintained.
  • If the source data file is located in the XLStart, Alternate Startup File Location, or the Library folder, a property is written to indicate one of these folders, and only the file name is stored.
    Note Excel recognizes two default XLStart folders from which to automatically open files on startup. The two folders are as follows:
    • The XLStart folder that is in the Office installation folder, such as C:\Program Files\Microsoft Office\Office folder\XLStart
    • The XLStart folder that is in the user's profile, such as C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLStart
    The XLStart folder that is in the user's profile is the XLStart folder that will be stored as a property for the link. If you use the XLStart folder that is in the Office installation folder, that XLStart folder is treated like any other folder on the hard disk.
    The Office folder name changes between versions of Office. For example, the Office folder name is Office, Office10, Office11 or Office12, depending on the version of Office that you are running. This folder name change causes links to be broken if you move to a computer that is running a different version of Excel than the version in which the link was established.
It is also important to note that what appears in the formula bar is not necessarily what is stored. For example, if the source data file is closed, you see a full path to the file, although only the file name may be stored.
Relative vs. absolute links
Links to external workbooks are created in a relative manner whenever possible. This means that the full path to the source data file is not recorded, but rather the portion of the path as it relates to the linked workbook. With this method, you can move the workbooks without breaking the links between them. The links remain intact, however, only if the workbooks remain in the same location relative to each other. For example, if the linked file is C:\Mydir\Linked.xls and the source data file is C:\Mydir\Files\Source.xls, you can move the files to the D drive as long as the source file is still located in a subfolder called "files".
Relative links may cause problems if you move the linked file to different computers and the source is in a central location.
Mapped drives vs. UNC
When a source data workbook is linked, the link is established based on the way that the workbook was opened. If the workbook was opened over a mapped drive, the link is created by using a mapped drive. The link remains that way regardless of how the source data workbook is opened in the future. If the source data file is opened by a UNC path, the link does not revert to a mapped drive, even if a matching drive is available. If you have both UNC and mapped drive links in the same file, and the source files are open at the same time as the destination file, only those links that match the way the file was opened will react as hyperlink. Specifically, if you open the file through a mapped drive and change the values in the source file, only those links created to the mapped drive will update immediately.
The link displayed in Excel may appear differently depending on how the workbook was opened. The link may appear to match either the root UNC share or the root drive letter that was used to open the file.
Scenarios that may cause links to not work as expected
There are several circumstances in which links between files can be inadvertently made to point to erroneous locations. The following are two of the most common scenarios.
Scenario 1:
  1. You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
  2. You create links to a workbook that is stored at the mapped location after you open the file through that mapped drive.
  3. You open the file by a UNC path.
  4. As a consequence the link will be broken.
If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1. In other words, the Share name is eliminated from the path.
Scenario 2:
  1. You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
  2. You open the file by a UNC path or a mapped drive mapped to a different folder on the share, such as \\Server\Share\Folder2.
  3. As a consequence, the link will be broken.
If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1.

Dynamics GP next sales order number is corrupted as has become the same as the next quote number range

This problem happened today, first time for a few years, but it used to happen regularly (every few months). The issue is that the auto populated sales order number in sales transaction entry gets overwritten with the number from the quotation number range.

My guess for the circumstances of this issue, is that the SOP Entry window is open with a quote showing, but not actually started yet. Somehow the order type gets changed from quote to order in the forms internal variables (integration or misbehaving code or connection lost or something?).

The Sop Entry window is then closed, or the order type drop down is changed to order from quote, causing the GP window to attempt to return the unused quote number to the pool to be reused via the next sop number field in sop setup.

However as we are saying that the window variable that holds order type is incorrect and thinks this is a quotation, the window actually returns this quote number to the next order number field, when it should have returned it to the next quote number field. Hence the next sales order number becomes a quote number! eek!Surprised smile

Ok, to fix this is easy, right? -open SOP setup by navigating to Microsoft Dynamics GP>>Tools>>Setup>>Sales>>Sales Order Processing then click [Numbers] button.

Next SOP number in Dynamics GP

Correct the next document number to what it should be (see SQL later to find this), note that you need to close both windows to ensure it saves, and be quick if users are generating numbers you will get a optimistic concurrency violation on save. The problem you then encounter is that if you have distributed sales force, spread over the globe, they will have picked up numbers from the incorrect sop number range, and have them sitting like a time bomb on new sales orders in the sop entry window. Even when you change the number in the sop set up window, if any of those users choose to subsequently close the Sop Entry window, having not used the erroneous sop number, then that number will again get returned to the sop next number field in sop setup, undoing your good work! This is exactly what happens, the number keeps jumping back to the quote range of numbers and you fight a losing battle against the users.

One answer is to ask all users to exit sop entry, make the change then let them back in, with a distributed work force, that may not be actually easy to contact or worse, not in front of the machines when you need to make the change, this is not practical. In this case, I have used the script below to keep resetting the number so immediately the users corrupts the next sop number, it gets corrected again.

Leave the script below running in SSMS until you are confident all users are now on the correct range of numbers.

DECLARE @NextSopNumber CHAR(21)

SET @NextSopNumber = '01447267'

WHILE 1 = 1
--Here SOPTYPE = 2 is an Order - could be changed to target another type
-- Make a like stub from the next number to see that the number has not jumped
SUBSTRING(@NextSopNumber, 1, LEN(@NextSopNumber) - 3)
SET SOPNUMBE = @NextSopNumber

-- 5 second delay then check again
WAITFOR DELAY '00:00:05'

In the above script, set the next sop number in the above script from the number that looks reasonable as a last number from running, looking at the SOPTYPE for the correct document type in question:


So long as the next SOPNumber is within 1000 of the actual last number used, then the GP client will scroll up through, finding the actual next sop number that is available for allocation. The exact number is not critical, and it wont matter until 1000 orders have been entered. After 1000 you will get an error message saying a new sop number could not be found. 
Do let me know if this was helpful, in the comments, keeps me motivated to write more!