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 Without a Cursor
May 10th, 2017 by Ian Grieve
I posted a script a while ago which used a cursor to return the functional currencies for all companies connected to a system database. However, I have recently revisited this script and created a version which does not use a cursor.
This script has been written to only return the companies which do not have a functional currency set; if you want to see all companies, regardless of the functional currency, remove the highlighted section.
/* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ CREATE TABLE #FunctionalCurrencies( INTERID VARCHAR(5) ,FUNLCURR VARCHAR(20) ) GO DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = STUFF(( SELECT CHAR(13) + 'SELECT ''' + INTERID + ''' ,FUNLCURR FROM ' + INTERID + '.dbo.MC40000 WHERE LEN(FUNLCURR) = 0' FROM DYNAMICS.dbo.SY01500 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') INSERT INTO #FunctionalCurrencies EXEC sys.sp_executesql @SQL GO SELECT * FROM #FunctionalCurrencies GO DROP TABLE #FunctionalCurrencies GO