Keep up to date with the latest information about Microsfot Dynamics GP through articles from the Perfect Image and azurecurve blog.
SQL Script To Return Functional Currencies For All Companies
November 3rd, 2015 by
I have done a fair bit of work recently for clients with the Web Services for Microsoft Dynamics GP. One of the checks the configuration tool does is to check if all of the companies have a functional currency defined.
However, if it returns a fail flag for this check, it doesn’t actually tell you which company (or companies) has failed the check. And when your client has well over a hundred companies you need an alternative method to going through each one manually.
That alternative method is the script below; it uses a cursor to look through the company databases and returns a list of all companies and their functional currency. To only see those companies without a functional currency, there is a where clause at the bottom which is currently commented out.
CREATE TABLE #FUNLCURR ( INTERID VARCHAR(5) ,FUNLCURR VARCHAR(15) ) DECLARE @SQL_Statement VARCHAR(1000) DECLARE cursor_InterID CURSOR FOR SELECT INTERID FROM SY01500 OPEN cursor_InterID DECLARE @INTERID VARCHAR(100) FETCH NEXT FROM cursor_InterID INTO @INTERID WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) SET @SQL_Statement = 'INSERT INTO #FUNLCURR (INTERID,FUNLCURR) VALUES (''' + @INTERID + ''','''')' exec (@SQL_Statement) SET @SQL_Statement = 'UPDATE #FUNLCURR SET FUNLCURR = (SELECT FUNLCURR FROM ' + RTRIM(@INTERID) + '.dbo.MC40000 MC) WHERE INTERID = ''' + @INTERID + '''' exec (@SQL_Statement) FETCH NEXT FROM cursor_InterID INTO @INTERID END CLOSE cursor_InterID DEALLOCATE cursor_InterID SELECT #FUNLCURR.INTERID ,SY01500.CMPNYNAM ,#FUNLCURR.FUNLCURR FROM #FUNLCURR INNER JOIN SY01500 ON SY01500.INTERID = #FUNLCURR.INTERID /*WHERE LEN(FUNLCURR) = ''*/ ORDER BY #FUNLCURR.INTERID DROP TABLE #FUNLCURR