Following on from the article published on Friday about why Microsoft Dynamics GP uses many 8 upper case character table and column names:

• #MSDynGP Why are most table columns in GP 8 uppercase characters?

This article consolidates many of the articles written about using MS SQL Server with Dexterity and also publishes original documentation from 1999 on how SQL Server support was added to Dexterity.

Great Plains Dynamics is developed using the Dexterity development environment. Dexterity was created in the late 1980s by the team at Great Plains Software when they decided to create their next generation product after their successful Great Plains Accounting (GPA) text based application. They wanted to create a single product that would work on both the Apple Macintosh and Microsoft Windows operating systems. When they realized that there weren’t any development tools that could handle that requirement, plus the requirements for easy translation and customization, they decided to write their own development tool. The story of the creation of Dexterity is beautifully told by one of the original team members in the articles below:

• End of an Era: Farewell Dave Gaboury & the Creation of Dexterity – Part 1
• End of an Era: Farewell Dave Gaboury & the Creation of Dexterity – Part 2

There are many names, but people like Dave Gaboury, Tim Brookins, Tom Irsfeld and Andrew Ingalls on the Dexterity Development team and Kevin Kidder, Scott Stephenson and Patrick Roth on the Dexterity Support team will always have my thanks for giving me this career I love so much.

When support for SQL Server was added to Dexterity, allowing all the existing code developed by Great Plains and the partner and ISV community to “just work” on SQL, there were decisions made to allow the legacy non SQL Dexterity environment to run on SQL Server.

This backwards support in Dexterity explains many of the quirks of how Microsoft Dynamics GP works today that are not understood by newer customers and partners who don’t have a long history with the product.

Quirks like:

• Why dates are stored as a datetime with no time?
• Why times are stored as a datetime with no date?
• Why an empty date in Dexterity is 1900-01-01 in SQL?
• Why the data integrity rules available in SQL are rarely used?
• Why table and column names are largely 8 uppercase characters?
• Why we have zDP_ stored procedures for accessing tables?
• Why do tables often have multiple indexes that SQL does not actually need?
• Why text datatype is used instead of varchar?
• Why even length string fields have an odd length with one more character in SQL?
• and so on …

It all comes down to backwards support for the huge library of code that makes up the Microsoft Dynamics GP ecosystem. There is code in the Microsoft Dynamics GP product that has not been changed since it was created in the late 1980s. How many other products can say that they can still support code written 35 years ago?

More Information

The following articles discuss related topics on using SQL Server with Dexterity:

• #SQLServer Working with Dexterity Dates and Times in Transact-SQL
• Quick Tip: Unusual behaviour when working with SQL Server from Dexterity
• Quick Tip: Passing Date and Time Variables to SQL Server
• Quick Tip: Passing Variables to SQL Server
• Language settings for Windows client must match SQL Server Language
• #MSDynGP Why are most table columns in GP 8 uppercase characters?
• What do the zDP_ Auto Generated Stored Procedures do?

As promised, below are two documents from February 1999 that were provided to Dexterity developers, to help them understand how to work with SQL Server. This is from when support was added to Great Plains Dynamics v3.15 for MS SQL Server 6.5.

• Using SQL Databases with Dexterity – Part 1.pdf
• Using SQL Databases with Dexterity – Part 2.pdf

Enjoy

David

This article was originally posted on http://www.winthropdc.com/blog.