Understanding how Microsoft Dynamics GP works with Microsoft SQL Server

Many people who have not been working with Microsoft Dynamics GP since the early non-SQL versions of Great Plains Dynamics sometimes wonder about the way that the application works with Microsoft SQL Server.

The answer is in the long history of the product and especially in the fact that it originally did not work with Microsoft SQL Server, but instead with the flat file database platforms of Ctree and Btrieve (later called  Pervasive.SQL). Flat file database platforms are also known as ISAM (Indexed Sequential Access Method) database platforms.  Support for the Microsoft SQL Server relational database was added for version 3.15.

To make the development environment Dexterity (and hence Dynamics) work with SQL Server required mapping the existing functionality supported by ISAM platforms to SQL Server equivalents.  As some features of SQL Server were not available in the ISAM platforms they were not leveraged until the code was changed to be SQL optimised.

This article will attempt to explain why Microsoft Dynamics GP sometimes uses Microsoft SQL Server in a unusual or unexpected way.

Data Types

Dexterity is a 32 bit environment and this means that it considers an integer as 16 bit and a long integer as 32 bit.  SQL Server supports 64 bit integers and so we have to map to smallint and int respectively.  Currency fields in Dexterity are stored with a fixed 5 decimal places (using formats to control how many decimal places are seen) and so we map to a numeric datatype with 5 decimal places.

Dexterity's use of datetime data type in SQL often raises questions.  As the Ctree and Btrieve platforms had separate Date and Time data types, we needed to map the two data types to the single datetime data type in SQL.  For Dates, we leave the time component of the datetime data type blank (00:00:00) and for Times we leave the date component of the datetime data type as blank (01/01/1900).

Below is a table showing the mappings for all data types:

Dexterity Control Type	SQL Server Data Type
Boolean	tinyint
Check Box	tinyint
Combo Box	char
Composite	Each component of the composite is stored in its own column in the SQL table. The name of each column is the physical name of the composite field followed by an underscore and the number of the component.
Currency	numeric(19,5)
Date	datetime (time component left as 00:00:00)
Drop-down List	smallint
Horizontal List Box	smallint
Integer	smallint
List Box	smallint
Long integer	int
Multi-select List Box	binary(4)
Non-native List Box	smallint
Picture	image
Radio Group	smallint
String	char
Text	text
Time	datetime (date component left as 01/01/1900)
Visual Switch	smallint

 

NOT NULL 

Dexterity does not understand the concept of NULL. Ctree and Btrieve did not use NULL and always initialised data to a blank or empty state. For example: zero value for numeric fields, empty string for string or text fields.  Because Dexterity does not handle NULL values all fields in Microsoft Dynamics GP tables are defined as NOT NULL. 

If a view is created to work with Microsoft Dynamics GP (including SmartList Builder), it should be created using the the ISNULL() function to set columns to a "blank" value when there is the posibility of a NULL value being returned.

 

Column Defaults 

You might have seen the four defaults added to SQL Server. The GPS_CHAR, GPS_DATE, GPS_INT and GP_MONEY defaults are bound to the columns in Microsoft Dynamics GP tables. Binding the defaults to use when an insert or update statement does not have a value for the column avoids errors caused because the columns are set to NOT NULL.

 

DEX_ROW_ID

Ctree and Btrieve did not have the concept of a primary key. Normally the first defined key was setup as the "Primary" key but that is not always the case.  Also there was no requirement to have the primary key as unique.  So for when the SQL implementation for Dexterity was created, the developers added the DEX_ROW_ID identity column to every table.  This field would then be added to any primary key that allowed duplicates so that the key created in SQL was unique (due to the added DEX_ROW_ID identity column)

The DEX_ROW_ID column is also used with the DEX_LOCKS and DEX_SESSION tables in the tempdb. These tables are used by Dexterity to implement its optimistic currency control (OCC) or passive locking.  This passive locking functionality allows Dexterity to perform the equivalent of field level locking.  Two users can modify different fields in the same record of data at the same time and both sets of changes will be saved.

 

DEX_ROW_TS 

The DEX_ROW_TS field that is now appended to some tables in v10.0 is a new feature which allows a server based timestamp to be stored against each row of a table.  This functionality was added to help with Business Data Catalog (BDC) indexing for SharePoint searches. To avoid indexing the same data multiple times, the timestamp can be checked to see what data has been updated since the last indexing process was executed.

This functionality can also help with synchronisation with external applications such as Customer Relationship Management (CRM) systems. Note that this column does use the datetime datatype with both a date and a time value.

 

zDP Stored Procedures

The zDP Stored Procedures were added to improve performance, by having the regularly used select statements based on the indexes pre-compiled.  See the What do the zDP_ Auto Generated Stored Procedures do? article for a detailed explanation.

Prior to SQL Server 2000 ad hoc select statements were not cached, but stored procedures were cached for repeated use. By creating stored procedures for data extraction, the performance was improved. The Database query engine in SQL Server 2000 (or later) now stores execution plans of select statements, so the performance difference is no longer as significant.

 

Referential Integrity

The referential integrity features of SQL Server are only used in a few places in the system.  This is because Ctree and Btrieve did not offer any referential integrity features and so the Dexterity code itself was used to validate parent/child relationships and check when records are still in use.  All the referential integrity was coded into the application and is not implemented at the server.

 

For more information please see the followup article, Understanding how Microsoft Dynamics GP works with Microsoft SQL Server continued.

 

For other related topics have read of the following posts:

Why does Microsoft Dynamics GP encrypt passwords? 

What is Column desSPRkmhBBCreh? 

Great Plains Historical Timeline

What do the zDP_ Auto Generated Stored Procedures do? 

Let me know if you found this useful.

David

29-May-2009: Added link to followup article.

Comments

• Anonymous
  May 21, 2009
  very informative and always good to know 'our' history :)

• Anonymous
  May 22, 2009
  Excellent stuff.  As always.  I'm wondering why boolean (and for that matter checkbox) would convert to tinyint.  In interest of efficiency and performance it'd seem bit would be ideal here.

• Anonymous
  May 25, 2009
  Tremendous information, David. I just thought of adding that, DEX_ROW_TS has off late helped me a lot in determining certain records based on more complex validations. I am not sure whether including this field (like we say for DEX_ROW_ID) on real-time business validations would create any issues. But till now, I have not faced any issues. Thanks Vaidy Mohan

• Anonymous
  May 26, 2009
  David Musgrave has a great new post up on Dynamics GP and SQL Server . In it he explains how GP works

• Anonymous
  May 27, 2009
  How does the Note Index (SY01500) work and what is it's purpose?

• Anonymous
  May 27, 2009
  Why hasn't microsoft implemented SQL transactions in posting processes?  I understand that btrieve and ctree did not support it but SQL does.  I often see failed posting where part of a batch or even a single transaction is posted and part is not or orphaned headers and details are left.  Using SQL Server transactions would prevent this.  I don't understand why this hasn't been implemented in at least the posting routines.  Is there some kind of dexterity issue preventing this? Thanks

• Anonymous
  May 27, 2009
  Hi Matt The Note Index field in the SY_Company_MSTR (SY01500) table stores the next Note Index to be used for the current company.  Every Master Record and Transaction has a Note Index value assigned to it when it is created.  This Note Index is then used as the primary key of the SY_Record_Notes_MSTR (SY03900) table when a record note is created. I will write an article with more information. David

• Anonymous
  May 27, 2009
  Hi Britt SQL Transactions are used for posting. I will add some information on Transactions to this post. Thanks David

• Anonymous
  May 28, 2009
  As a followup to last week's post about Understanding how Microsoft Dynamics GP works with Microsoft

• Anonymous
  June 01, 2009
  Posting from Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2009/05/inner-workings-of-microsoft-dynamics-gp.html

• Anonymous
  June 08, 2009
  The comment has been removed

• Anonymous
  June 24, 2009
  Posting from the Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2009/06/how-does-check-links-work.html

• Anonymous
  February 09, 2010
  Great article David, a quick question - where do I find information on integration between Dynamics GP and BDC? We are looking to give our users a way to view AP invoice in Sharepoint and were wondering if there was a good way to do this by using the BDC. Any help will be much appreciated.