This posting is part two of a two-part series on calling Microsoft Dynamics GP eConnect procedures directly from Transact-SQL (TSQL) code. Part one of this post focused on an introduction to eConnect procedures. This second part will provide more details and examples on using the eConnect TSQL procedures.
Now that we have a basic understanding on where to find information on the SQL stored procedures (from the eConnect Programmer’s guide discussed in the last post), we can get to work on creating a simple integration of data into Dynamics GP.
One of the advantages of using the eConnect procedures in TSQL directly is that you will be able to create a high-performance integration that can be scheduled to run via the SQL Server Agent’s “Job” functionality. In my example for this post, I am going to show an integration that automatically updates inventory item information from one company database in GP to another. (This was actually something we did recently for one of our GP clients.)
The first step of this process was to identify what information related to inventory items needed to be synchronized between the two databases. The information they wanted to copy to the subordinate database included:
- The item master (IV00101) – both inserts and updates of data. To pull the list price for each item, data from the IV00105 table is needed.
- The item price list information. This is data you would find in the GP tables IV00107 and IV00108.
- Item purchasing information. This in the GP table IV00106.
- Item User Category Values – the master list of values you can select for the 6 user categories. This data is stored in the table IV40600.
- Bring over custom Extender data – each item has an extender field/record associated with it.
With eConnect procedures, you only need to provide parameter values for the required parameters and for any additional parameters that you want to populate. In the case of this integration, it is using almost all of the parameters for the procedures. If you are not intending to use a particular parameter that is not a required parameter, don’t include any reference to it in your calling of the eConnect procedure. In the example below, if I did not want to use the item short name (@I_vITMSHNAM), then I would remove that line entirely.
You will likely need to add some conditional logic to deal with business rules in the procedures. In the case of the “taUpdateCreateItemRcd” procedure, you cannot pass values into the “User Category” fields (1 to 6) if those categories are not defined in inventory setup. In this case, after checking whether those fields are defined, logic was added to “null” the value of the parameter. Remember that blanks are handled differently than null, so if you can’t pass blank, if you shouldn’t be passing a value.
When calling multiple eConnect procedures from within your custom procedure, you can declare the common parameters initially in your procedure definition. Then, just reuse the variables. Just remember you may need to reinitialize the values after each use. We use a custom SQL function, too, for returning the full error text of the eConnect procedure. eConnect will potentially return more than one error code at a time, so this function has been a useful thing to have and be able to reuse.
As shown in the screenshot above, we also use custom tables to log our errors, as we want to capture as much information about the error and where it is occurring in the process. The error log table is also useful if you intend to create either an error report or an HTML email that provides the end users with a list of any errors encountered.
I do recommend that you do all of your SQL coding inside of TRY…CATCH blocks. When doing this, remember that since you are handling the errors, SQL may not report back an error on its own. Just make sure you log or handle errors as most appropriate.
Lastly, eConnect procedures tend to have some common parameters that you will see in each procedure. One of the most valuable of these is the “Update if Exists” (@I_vUpdateIfExists) parameter. This parameter (if available) lets you tell eConnect to do either an insert of a new record or an update of an existing record. Make sure to review the Programmer’s Guide for any special conditions that apply to the parameter for the procedure you are using. In some cases, the update may not work the way you think.
Hopefully, these postings will aid you in building some robust TSQL based integrations for Dynamics GP!
For additional tips and tricks regarding Dynamics GP and ERP products, be sure to subscribe to our blog.
