Dynamics GP SQL Scripting Level Data Repair

Probably one of the reasons is metadata and business logic abstraction level in Microsoft Dexterity (and its dictionaries, especially the core one Dynamics.dic).  Dexterity was introduced as the abstraction level and programming shell, coded in C and allowing you to be compatible with various database platforms.  Historically these platforms were Btrieve/Pervasive SQL, Ctree and Microsoft SQL Server.  This abstraction from the DB platform limits programmers to go deeper in deploying such DB platform depended SQL functionality as transactions (with commit if everything is successful, otherwise rollback).  When Microsoft acquired Great Plains Software and abolished non-Microsoft SQL Server DB versions (such as Pervasive SQL 2000, former Btrieve; and Ctree) Dexterity DB neutral cursors (programmed in Dexterity Sanscript) were partially replaced by SQL Stored Procedures.  But these code revisions are do not have the magnitude of the whole Dexterity business logic recoding in SQL Stored Procedures, as application user interface is driven by Dex as well (large portion of Dexterity code is in fact replicated in eConnect encrypted SQL Stored Procedures, but eConnect fits rather to the integration requirements, including native technologies, such as Integration Manager, Web Services as well as SDK and libraries to be included into your MS Visual Studio C# or VB.Net project).  All mentioned above should give you some apology for being vulnerable to SQL transaction failures.  Let’s review data repair techniques:
1. Batch Posting recovery.  Posting business logic is the most complex and it is implemented with substantial percentage of the whole number of Sanscript code lines.  Great Plains developers and architects recognize the fact that batch posting could take some time to be finished and during that time computer crush or SQL Server outage might happen.  And the lack of transaction roll back may result in batch posting interruption and limbo scenario.  If this is what happened to you, have user log off and login back – she or he should get notification to go to batch recovery window, where posting status is manifested.  If Posting Interruption Edit Required – please do not try to re-post the batch.  You need to enable it, open it in the module and print Batch Edit List, where errors should be reported for one or more documents (often something like GL distribution account is missing – in this case simply open transaction, click on Distribution and add the account).  If you got data corruption outside of the batch posting business logic, keep reading
2. Check Links.  This utility is available if you click on Maintenance -> Check Links.  It simply validates each table cluster (GL Account Master tables form example) to see if there are orphan records on some of the tables, or if required record is missing, while there is evidence in the majority of the mathematically related tables that the records should be present and the record could be recreated based on the records in related tables (GL00105 – Account Index Master, for example could be recreated based on the records in Account Master table GL00100).  If you see the issues with data corruption, where Check Links helps you to fix the issues – chances are high that these inconsistencies are caused by poorly designed Dexterity customization, SQL insert scripts or stored procedures, eConnect programming (where portion of the code might use SQL Update statement, as programmer was under impression that this type of shortcut is OK).  If you see real data corruption on the SQL File Group and Physical files level – we are happy to entrust its solution to the hands of SQL DBA.  GP should work in SQL Cluster or Database mirroring environment.  If you are using DB Mirroring (SQL 2008, 2005), be sure that you are also have the routine, transferring SQL logins (there is special routine in SSIS or you can find SQL scripting logins and security transfer method on the GP Customer Source).  If you are using SQL Replication: Transactional (bidirectional) or Merge – we recommend you to avoid scenarios, where GUID is required to be added to the table.  The reason to avoid GUID columns is simple – when you are doing version update large portion of the tables are dropped and recreated, where your replication columns could be lost
3. Data Repair via SQL Update, Delete and Insert statements.  If you are certified and experienced Microsoft SQL Server DBA or SQL programmer, you may feel that you should be able to learn new table structures as the project progresses.  However due to the fact that Great Plains doesn’t enforce such constrains as Foreign Key, Transaction Commit/Rollback – the database records are vulnerable to SQL deletes and updates, where the whole or part of the records could be destroyed and business logic compromised.  If you feel that you are ready to practice with SQL Update or Delete, please create test company (in GP Utilities).  Then, load this test company with Production company backup.  Switch on SQL Profiler to trace the changes distribution to the tables (where you have your Great Plains users to replicate transaction or master record add/change or delete via user interface).  Then try your Update, Delete or Insert script in SSMS.  If you would like to stay on the safe side, try Check Links on the table cluster, where you just practiced altering (if Check Links reports doesn’t produce lines you are lucky and really good SQL DBA).  If you got errors in Check Links report, or if you feel some confuse, please review Great Plains table structure, next paragraph
4. Table Structure.  There are several methods to review the structure of the tables in Dynamics GP.  First one is in user interface.  Open Dynamics GP -> Tools -> Resource Description -> Tables (here select the product, series and sort tables either by physical, table group technical, table physical or table technical name).  If you would like complete table diagram with mapped business processes, consider installing GP SDK (look at GP DVD media, open Tools folder and open SDK\Dynamics GP, click on SDK.exe).  If you are on older version of Great Plains Dynamics Select or eEnterprise, install SDK from CD #2
5. Common tables where data repair could be done on the SQL scripting level.  These are PM Transaction Open File (PM20000), where Current Transaction Amount field could be out of synch (CURTRXAM – due to the apply process crush on the user computer level, or poorly coded Dexterity customization).  In this scenario you should design your data fixing script joining PM10200 (PM Apply to Work Open File).  And now let’s take a look at Receivable Management side.  Here we have Receivables Open Transaction Table: RM20101.  Its counterpart in applying payments, credit memos is RM20201 – RM Apply Open File
6. Data Repair for older versions of Great Plains Select: 8.0, 7.5, 7.0, 6.0, 5.5, 5.0, 4.0, 3.2. While version 8.0 was available exclusively on MS SQL Server DB platform, earlier versions were available also on the alternative Pervasive SQL 2000/Btrieve or Ctree.  For Pervasive SQL/Btrieve you would need generate DDF files (file, field, index DDF files).  If you have user workstation installed on your Windows XP or Windows 2000 computer, open Windows Explorer, go all the way to GP workstation folder, then drag DDF.Set and drop it on Dynamics.exe.  At this point you are ready to generate DDF files, we recommend you to p[lace them into your Dynamics folder on the Server (you can use either UNC path or mapped drive).  Next step is to create MS Access database with linked tables (use other – OBDC linked table object, select your GP ODBC System DSN and integrate the required tables)
7. Great Plains DOS, Mac or Windows archaic versions 9.5, 9.2, 9.1, 8.0 and earlier.  These versions were based on Btrieve database platform, however the table clusters were merged into Btrieve physical files.  In order to read and export GPA table you need first generate DDF file (please read GPA ODBC manual on DDF file generation procedure)
8. If you feel that your concerns are not addressed or your question is not answered, please feel free to contact our office.  Support domestically in the USA, Canada, Mexico and internationally.  This option is possible via Web Sessions, Skype or Phone conferences and direct visits onsite (in the case of the large scale project).  Our consulting team speaks English, Chinese, Portuguese, Spanish, Russian, Filipino.  Feel free to call us 1-866-304-3265, 1-269-605-4904, or email help@efaru.com