Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Understanding how Microsoft Dynamics GP works with Microsoft SQL Server

Understanding how Microsoft Dynamics GP works with Microsoft SQL Server

  • Comments 13

David MeegoMany 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.

  • very informative and always good to know 'our' history :)

  • 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.

  • 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

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

  • How does the Note Index (SY01500) work and what is it's purpose?

  • 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

  • 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

  • Hi Britt

    SQL Transactions are used for posting. I will add some information on Transactions to this post.

    Thanks

    David

  • As a followup to last week's post about Understanding how Microsoft Dynamics GP works with Microsoft

  • Posting from Dynamics GP Blogster

    http://dynamicsgpblogster.blogspot.com/2009/05/inner-workings-of-microsoft-dynamics-gp.html

  • Great stuff.  I am always searching for information to better understand "what" and "why" of Dynamics GP.  You never fail to provide explanations.  And history to boot!

    Thanks for your work--I appreciate it.

    -Roger T.

  • Posting from the Dynamics GP Blogster

    http://dynamicsgpblogster.blogspot.com/2009/06/how-does-check-links-work.html

  • 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.

Page 1 of 1 (13 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 8 and 7 and type the answer here:
  • Post