Finding modified eConnect procedures in Dynamics GP

<Dynamics Geek Mode On>

The eConnect feature of Dynamics GP has great extensibility that allows administrators to customize how data is handled. It offers the capability to “hook” into other systems/tables. This is done through the eConnect “pre” or “post” procedures that exist in your GP company database. These procedures allow the administrator to craft custom code that has access to the eConnect message data. More information about these capabilities can be found within the eConnect documentation within Dynamics GP.

In a fresh Dynamics GP installation, the eConnect procedures are installed along with the customize-able empty stub pre/post procedures. These empty stubs need to be in place because they are referenced by the Dynamics GP eConnect code.

In GP installations, it is often difficult to determine what procedures may have been modified from their original empty “stub” code. The simplest way to accomplish this typically involves creating a query that compares the stored procedures modify_date against the create_date. For example:

SELECT name, create_date, modify_date
 FROM sys.objects
 WHERE (type = 'P') AND (name LIKE 'ta%Pre' or name LIKE 'ta%Post')
 AND (create_date <> modify_date)

The intent of the above query is to display all eConnect “pre” or “post” procedures that were altered after their original installation date. It’s a fast and easy approach, but it fails to address scenarios where the author may not have made the change through a SQL “ALTER PROCEDURE” statement. Many DBAs and developers will issue a “DROP PROCEDURE” followed by “CREATE PROCEDURE”. That messes up the above approach because the create and modified date is the same.

Here’s a trick to get around this problem. The script below takes a sample datetime from one of the eConnect base procedures and uses that datetime for comparison to list pre/post procedures that are newer.

-- Author: Panatrack
-- Usage rights: Shared freely with the community, but not supported.

 DECLARE @install_date DATETIME;

 -- Grab a date from one of the core eConnect procs. This should represent the install or update time for eConnect.
 SELECT @install_date = create_date
 FROM sys.objects
 WHERE type = 'P' AND name = 'taGetFAIndex'

 -- Find any pre/post procedures that were created/modified after the exemplar's time.
 SELECT name Modified_Procedures
 FROM sys.objects
 WHERE type = 'P' AND (name LIKE 'ta%Pre' or name LIKE 'ta%Post')
 AND DATEDIFF(mi, @install_date, modify_date) > 1 -- catches procedures modified after one minute of installation

This will list modified eConnect procedures that were either altered or dropped/created after the initial installation. While it doesn’t feel like a perfect solution, it gets the job done.