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.


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

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!