How To Find Out Modified eConnect Procedures in Microsoft Dynamics GP

The eConnect feature in Microsoft Dynamics GP offers significant flexibility for tailoring data management. It enables developers to connect with other systems and tables/schema by utilizing eConnect’s “pre” and “post” procedures within the GP company database. Essentially, these procedures allow developers to adjust or enhance the core eConnect stored procedure and incorporate custom logic. For more information on how to use these capabilities, you can consult the eConnect documentation provided within Dynamics GP.

https://learn.microsoft.com/en-us/previous-versions/dynamicsgp/developer/ff623651(v=msdn.10)?redirectedfrom=MSDN

I’ve been assisting a client who is facing challenges with data imports using SmartConnect Integration. Specifically, issues are arising with the eConnect stored procedure due to custom logic within the “Post” script. To address this, the client needs an easy method to identify which eConnect PRE and POST stored procedures have been modified. Having a clear list of these modified procedures will enable us to script only the affected ones before carrying out a major version upgrade, installing a feature pack, or making any endpoint changes.

In GP installations, it can be challenging to identify which procedures have been altered from their original code. One straightforward approach to address this is by using a query that compares the modify_date of stored procedures with their create_date

--find econnect pre-post objects
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)

While this method is efficient and straightforward, it does not address scenarios where modifications are made by using “DROP PROCEDURE” followed by “CREATE PROCEDURE” commands. In such cases, the creation and modification dates will be the same, complicating the process of tracking changes. We aim to find all eConnect “pre” or “post” procedures that have been altered since their initial installation.

To address this, you can use the following query to run against your company database. This will help you compare the modified dates of “pre” and “post” procedures with the original schema node created date, allowing you to identify which procedures are more recent.

This query details the updates, removals, and newly introduced procedures in eConnect since the initial installation. It includes changes to the “Pre/Post” stored procedure, with its modified date shown by c_modify_date, and the modification date of its parent schema node indicated by p_modify_date.

--find econnect pre-post objects – Advanced.

SELECT I.c_name as [modified_sp], I.create_date as c_create_date, I.modify_date as c_modify_date, 
       O.p_name as [parent_sp], O.create_date as p_create_date, O.modify_date as p_modify_date
FROM
(
	SELECT 
		case when RIGHT([name], 3) = 'Pre' then LEFT([name], LEN([name]) - 3)
			 else case when RIGHT([name], 4) = 'Post' then LEFT([name], LEN([name]) - 4)
					   else [name]
				  end 
			 end as p_name,
		[name] as c_name, 
		CONVERT(VARCHAR(16), create_date, 120) as create_date, 
		CONVERT(VARCHAR(16), modify_date, 120) as modify_date
	FROM sys.objects
	WHERE (type = 'P') AND ([name] LIKE '%Pre' or name LIKE '%Post')
 ) as I
INNER JOIN
(
SELECT [name] as p_name, 
	   CONVERT(VARCHAR(16), create_date, 120) as create_date, 
	   CONVERT(VARCHAR(16), modify_date, 120) as modify_date
	 FROM sys.objects
	WHERE (type = 'P') 
) as O
on O.p_name = I.p_name
where (O.create_date <> I.create_date or O.modify_date <> I.modify_date)

Have a question? Please reach out to us at support@eonesolutions.com