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 (https://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
Can ISC Software Solutions help?
ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.
4 thoughts on “SQL Script To Return Functional Currencies For All Companies Without a Cursor”