Tuesday, August 2, 2011

Programmatically Checking Company Database eConnect Version Numbers

Back in November 2008, I wrote a post about checking eConnect version numbers programmatically.

At that time, I had found that the eConnect Release Info application simply called the stored procedure DYNAMICS..taeConnectVersionInfoDYNAMICS.  That stored procedure returned a list of databases and the eConnect version for each database.

The problem with that utility is that if a database is offline or not accessible for some reason, the Release Info app will display an error and will not return any results.  If you have a client with dozens or hundreds of company databases, and has one or more database offline, this error can be quite annoying.

While troubleshooting a new eConnect 2010 issue, I had to do some extensive eConnect version sleuthing, and finally discovered how the Release Info app gets version numbers from the database.

The eConnect Release Info application calls the taeConnectVersionInfoDYNAMICS procedure.  That stored procedure gets a list of all INTERID values from the Dynamics SY01500 table--basically a list of all of the company databases.

For each company database, it then queries the taeConnectVersionInfo function, like:

SELECT dbo.taeConnectVersionInfo()

This returns the eConnect version for the database.

So, the obvious question is:  How does this function get the eConnect version number for the database?

That's easy--it's hard coded!

The function simply returns a hard coded version number string, such as  So there is no table or object or magic query that is being run to extract the version number from the depths of the company database.  It is simply the taeConnectVersionInfo function that is updated with each eConnect release.

So, if you are looking for a programmatic way to check the eConnect version number for individual databases, you can just select the taeConnectVersionInfo function for each database.

Pretty simple.  Wish they would have just documented this somewhere.

Although that will provide you with the database's eConnect version, please note that with eConnect 2010, there are different versions of the eConnect DLLs and eConnect Service with each service pack and hotfix.  So for instance, eConnect 2010 SP2 has DLLs with version 11.0.1761.0, but if you install the eConnect June 2011 Hotfix (KB2561289), the DLL versions will change to 11.0.1812.0.

So it still probably makes sense to verify the eConnect DLL versions as well as the DB versions.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.


1 comment:

Mariano Gomez said...


You still forgot to mention how to get over the issue of the database being offline.

The following script will check for eConnect versions only for databases that are currently online. I have used this same script as a stored proc with parameters (without the cursor) to check for backward/forward compatibility of components in some integrations I have written:

DECLARE @INTERID VARCHAR(5), @state_desc VARCHAR(10), @eConnectVersion VARCHAR(100), @SqlStmt VARCHAR(200);

SELECT a.INTERID, b.state_desc FROM SY01500 a
INNER JOIN sys.databases b ON (a.INTERID = b.name);

OPEN c_company;
FETCH NEXT FROM c_company INTO @INTERID, @state_desc;
IF @state_desc = 'ONLINE'
SET @SqlStmt = RTRIM(@INTERID) + '.dbo.taeConnectVersionInfo';
EXEC @eConnectVersion = @SqlStmt;
SELECT @INTERID, @eConnectVersion;

FETCH NEXT FROM c_company INTO @INTERID, @state_desc;
CLOSE c_company;
DEALLOCATE c_company;

There may be some formatting issues, but copy/pasting it back to SSMS should do.

Mariano Gomez, MVP