Tuesday, February 23, 2016

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




at

1 comment:

  1. UnknownApril 7, 2016 at 2:33 PM

    Hi Steve,

    To avoid using a cursor, you could do the following - the query is irrelevant, you could modify it to suit your needs. This should be quite a bit faster as it removes the looping nature of the cursor:

    create table ##tempresult (INTERID varchar(5), ACTNUMBR_1 varchar(25))

    declare @SQL varchar(MAX) = ''

    select @SQL = @SQL+'INSERT ##tempresult select distinct '+char(39)+INTERID+char(39)+', ACTNUMBR_1 from '+INTERID+'.dbo.GL00100;'
    from DYNAMICS.dbo.SY01500
    exec (@SQL)

    select * from ##tempresult

    Hope it helps!

    Aaron Berquist

    ReplyDelete