Dynamics GP, Item description is one hundred character capacity?

A little curiosity of mine is around this finding & why it is so…

Dynamics GP Item description field


Fill the item description field of an item in dynamics GP and then paste the text into a notepad application to measure its length. You will find it has a capacity of one hundred characters…

measure capacity of UI as 100


Yet have a look at the database, it has a field size of 101…

Database field length is 101

 

but look, the UI is limiting the keyable length to 100…

Keyable length of Item Description 100

 

So there is an “extra” inaccessible character in the descriptions that you cannot use? What secret confidential information do you keep in your extra description extra character?

 

[Edit 2017/08/08] David in the comments explains this for us as:

Every string field of even length will have an extra character at the database level. 

This is a Dexterity feature from legacy behaviour. 

Before SQL was used as the database, Ctree and Btrieve was used. They performed better when each record in a table was a multiple of 16 bits, 2 bytes, as the early x86 processors were 16 bit. 

To ensure this string fields were padded to make the storage length an even number. Strings require the number of characters in the string plus a length byte when stored and so can be 0-255 characters long. 

On your screenshot, the keyable length is 100, plus a storage byte = 101, plus pad to even gives 102 with an extra hidden character. 

Odd length strings don't need padding to be an even total size. 

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.

2017-08-06_21-19-10

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:

2017-08-06_20-29-30

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

SELECT
     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;

2017-08-06_21-28-00


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.


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.

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

 
2017-08-02_16-14-09

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:

=+VLOOKUP(C10,'\\domain.net\folds\USERS\design\bill\[Costings.xls]Sheet1'!GpData,2,FALSE)
=+VLOOKUP(C10,'N:\USERS\design\bill\[Costings.xls]Sheet1'!GpData,2,FALSE)

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.

\\domain.net\files\folds\USERS\design\bill\

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.