Query the functional currency for all Dynamics GP company databases

By Steve Endow

When installing GP Web Services, all company databases must have a functional currency assigned, otherwise the Web Services Configuration Wizard will give you a validation error.

If you have several company databases, it can be a bit of a hassle to login to every one to see which one does not have a functional currency assigned.

And I suppose there might be some other reason why you would want to see or verify the functional currency value in all of your companies.


Here is a query that will show the functional currency for each company database.

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 FUNLCURR FROM  ' + @INTERID + '..MC40000) AS FunctionalCurrency';
       FETCH NEXT FROM INTERID_cursor INTO @INTERID
END

CLOSE INTERID_cursor
DEALLOCATE INTERID_cursor

EXEC(@SQL)


It outputs the INTERID database name, and the assigned Functional Currency.

Enjoy!

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