Dynamics GP for SQL Programmer: Highlights, Useful Information

There is some peculiarity in GP tables, which is probably attributed to Great Plains Dexterity architecture, as all the tables participating in Dynamics GP user interface (except some native tables, created by Business Portal modules) are created through Dexterity and described in Dexterity metadata dictionaries, such as Dynamics.dic.  GP Dexterity itself was created as the sell, coded in C programming language to abstract Dynamics code from such short life creatures, as computer platform (in time – earlier 1990th IBM PC, Macintosh, Unix flavors – including competing graphical operating systems, for example MS Windows versus Mac or Sun Solaris), and of course various database platforms (being in time and even now in stiff competition): Sybase, emerging MS SQL Server, Btrieve, Ctree, FoxPro, IBM DB2, Oracle.  Dexterity technology in time was very reliable and allowed potentially roll out Great Plains Dynamics on any modern computer within the year (as C code was in time computer platform and specific Assembler programming language neutral).  If you decided to skip Microsoft Dexterity intro sentences, we may have you to review them later, if you need to do so, based on the information in the paragraphs:

1. Dexterity, or what SQL DBA or programmer should know about its influence on GP database side.  As most of the core Dynamics GP elements are described in central Dynamics.dic dictionary, you cannot change table structure, such as add new fields (however we understand the temptation and braveness to do that on SQL Alter Table clause level).  If you change the table on SQL level, Dexterity based user interface will fail to read the records from this table and you will have to roll back the old table structure and probably copy data from saved old table copy.  Second Dexterity stamp is DEX_ROW_ID – this field is reserved for use by Dex, do not try to do something with it (the exception is when you may decide to use DEX-ROW_ID in advanced SQL select statement or stored procedure).  With some restrictions new versions of Dynamics GP allow you to introduce new indexes (whatever you see in default installation are indexes, described in Dex meta dictionary and propagated to SQL Server DB – please do not try to alter existing indexes and primary keys), however we do not recommend you to do this surgery, until you are familiar with Microsoft Dynamics GP SQL programming guidelines.  Good enough, let’s now move on to GP table structure

2. Great Plains table structure.  There are several methods to review the tables, fields and forms from technical perspective.  We were talking to various Dynamics GP internal technical support people and figured out that the most popular is the one available directly in GP user interface.  Login Dynamics GP workstation, preferably with SA (SQL System Administrator) credentials, pick the company (your choice), then Dynamics GP -> Tools -> Resource Description -> Tables.  At this point you need to know in which series your table is: Financials, Sales, Purchasing, Payroll, etc.  If you are not sure, we recommend you to consult with your accounting department.  Second method in the row in its popularity is Dynamics GP SDK, which could be installed from GP DVD (older versions of Dynamics GP, such as 10.0, 9.0, 8.0, 7.5 had it on CD#2)

3. Dynamics GP Reports design and data export.  We believe that at this point you see the most recommended way for SQL programmer to help with Dynamics GP support.  It is not recommended to do direct data update or insert (or even worse to change GP table structure and its native indexes).  Reporting on the other hand doesn’t alter the database, it is only pulling data from the tables.  In fact we recommend creation SQL stored procedures or views for such reporting tools, as Crystal Report, SSRS, MS Access reports, MS Excel

4. Importing data into Dynamics GP.  Here we do not recommend you to consider direct SQL feeding option.  Instead we recommend Integration Manager, this tool validates business logic and doesn’t allow data compromise.  IM is also in our opinion the tool of choice when you are migrating away from your old accounting application (QuickBooks, Peachtree, MYOB, Oracle Applications, Sage ERP products, Accpac, SAP Business One to name few examples)

5. Cracking eConnect stored procedures encryption.  We were observing some hackers and programmers reports, where they were able to find the password for Dynamics GP econnect encrypted stored procedures.  eConnect SDK is replicating some of the Dexterity business logics and in some opinions tries to introduce certain elements of Object Oriented Programming, especially for Microsoft Visual Studio C#, VB or other .Net compliant programming language programmers.  We would like to stay away from this discussion, just letting you know that other Dynamics GP enthusiasts reported the possibility

6. What to do if your company deploys the old version, for example Great Plains Select on Pervasive SQL Server 2000.  Here you have to come through the migration path, likely Great Plains Select 7.5, 7.0 or 6.0 to Dynamics GP 7.5 on Microsoft SQL Server.  Data repair was popular through MS Access interface, where you link to ODBC linked tables (typically system ODBC DSN, you would have to install Great Plains Pervasive SQL or Ctree driver).  You can do the same in MS SQL Server 2000 or 2005/8 user interface, however the streamlining wizard is more complex to come through

7. For additional information please call us: 1-866-528-0577, 1-630-961-5918 or email us: help@albaspectrum.com