"Object Reference Not Set" error when running Integration Manager with eConnect Adapter

I have seen a number of forum posts around this subject and have even received a few calls for help in troubleshooting the issue. In the occassions I have assisted someone, I have noticed that most of the time the developer or consultant was using an event or field script of some kind, which almost always attempts to get some information from Microsoft Dynamics GP.

So, in an attempt to reproduce the problem, I have recreated the following VBScript based on a recent case:

' Created by Mariano Gomez, MVP
'  This code is licensed under the Creative Commons 
'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.

Dim objConn, objRec, cmd, sJE
 

set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "database=" & GPConnection.GPConnInterCompanyID
GPConnection.Open(objConn) 
 

Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = objConn
 
cmdString = "SELECT NJRNLENT FROM GL40000;" 
Set objRec = objConn.Execute(cmdString)

if Not objRec.Bof and Not objRec.Eof then
  objRec.MoveFirst
  CurrentField = objrec.fields(0).value
end if
  
'Close recordset when finished
Call objRec.Close
  
'Close connection when finished
Call objConn.Close
 
Set cmd = Nothing
Set objConn = Nothing

NOTE: This script purposefully contains errors and does not follow best practices. It was recreated to illustrate the issue on the subject.

In summary, the above script was added by the consultant to retrieve the next journal number for a GL Transaction integration with the eConnect Adapter. The consultant reported the script working on and off on the server and not working on the workstations. However, in each case the error reported by Integration Manager is as follows:

Opening source query...
Establishing source record count...
Beginning integration...

DOC 1 ERROR: Error Executing Script 'GLTransaction.Journal Entry#' Line 9: - 
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Integration Failed
Integration Results
    1 documents were read from the source query.
    1 documents were attempted:
        0 integrated without warnings.
        0 integrated with warnings.
        1 failed to integrate.

The error indicates the is a problem with the data source name not being found, which leads to an object reference problem when the connection is attempted. But why would this code work on the server at times and not work on the client? Then it hit me!

The GPConnection object retrieves the connection and login information for the user currently signed on to Microsoft Dynamics GP... and therein lies the issue! The GPConnection object actually requires the Microsoft Dynamics GP user interface to be active for the object to retrieve the connection information, which is typically not the case for eConnect Adapter-based integrations.

As a side note, the times the integration did work, the user interface HAD to be active, but this was not apparent to the consultant.

So, how can we adjust this integration to follow best practices and work without the Microsoft Dynamics GP user interface having to be active?

The answer is relatively simple. The above code will need to switch out the way it obtains the connection string for an actual (as in hardcoded) connection string.

'
objConn.ConnectionString = "Provider=SQLNCLI10;Server=yourSQLServerName;_

Database=YourCompanyDB; Trusted_Connection=yes;" 

Because the script uses a trusted connection to the database (a best practice), it is advisable that proper permissions be granted to the user's domain account on SQL Server in order for the integration to be successful. The domain account will also need to be added to the DYNGRP role. What many customers have done is created specific domain accounts to execute eConnect integrations under a trusted connection. This further limits the exposure to security breaches.

For a final look at a technique to implement the above script, see the following article on this site:

Integration Manager: Integrating journal entries with Analytical Accounting Information

Hope you found this post useful.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/