Retrieving a value from every Dynamics GP company database

By Steve Endow

Back in November 2015, I had a brief post about running a SQL statement against every Dynamics GP company database.  In that case, I needed to run a SQL update statement, so it was relatively simple.

But what if you want to retrieve some information from every single Dynamics GP company database?

The process is similar in that it uses a cursor, but if you want to have a nice clean result set, rather than a separate result for every database, it takes a little more creativity.

In this case, I needed to check the first GL account segment in every company database as part of a custom integration.  The customer said that each company database has a separate value for the first segment of every GL account, and that I can use that value to map external data to the appropriate Dynamics GP database.  So if the GL account in the source data is "15", I can find the company database with GL accounts of 15-XXXX-XXX, and that will be the database into which the data should be imported.

In addition to needing to map that first segment value to each database, I wanted the query to count how many different distinct segment 1 values existed in the company.  Each company should only have one distinct value, so if we find that there is more than one, then there is a problem and this technique won't work.

So here's what I came up with, which is a modified version of my November cursor example.

DECLARE @INTERID varchar(10) = ''

DECLARE @SQL varchar(MAX) = ''

DECLARE INTERID_cursor CURSOR FOR

SELECT INTERID FROM DYNAMICS..SY01500

OPEN INTERID_cursor

FETCH NEXT FROM INTERID_cursor INTO @INTERID

WHILE @@FETCH_STATUS = 0

BEGIN

       IF @SQL <> '' BEGIN SET @SQL += ' UNION '; END

       SET @SQL += ' SELECT ''' + @INTERID + ''' AS INTERID, (SELECT COUNT(DISTINCT ACTNUMBR_1) FROM ' + @INTERID + '..GL00100) AS Segment1Values, (SELECT TOP 1 ACTNUMBR_1 FROM ' + @INTERID + '..GL00100) AS CompanyID';

       FETCH NEXT FROM INTERID_cursor INTO @INTERID

END

CLOSE INTERID_cursor

DEALLOCATE INTERID_cursor

EXEC(@SQL)

This time, since I'm not running an update statement, I needed to use a Union to combine the multiple select statement results into a single result set.

As always, there are probably several ways to handle this requirement, but it was pretty easy to modify my existing cursor based query and get it done quickly.

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com