Keep up to date with the latest information about Microsfot Dynamics GP through articles from the Perfect Image and azurecurve blog.
SQL Script To Set Budget Transaction History To Be Kept In All Companies
October 21st, 2015 by Ian Grieve
We recently upgraded a client with well over a hundred companies from Microsoft Dynamics GP 10 to GP 2015; one of the new pieces of functionality they wanted to start using was the Budget Transactions.
One problem is that the keep history checkbox for Budget Transactions in General Ledger Setup () is unmarked.
Enabling it for this many companies was going to take a substantial amount of time (even if we generated a macro to do this in automatically) as the client has over 120 companies in Dynamics GP. It is a simple setting in one table which needs to be updated, so instead of using a macro I created a SQL script containing a cursor to loop through all of the databases attached to the system database and enable this setting.
As with all scripts which change data, please make sure you have a good backup of your database (or in this case all of your company databases) before running the script.
DECLARE @IntercompanyID VARCHAR(5) DECLARE @SQLStatement AS VARCHAR(2000) DECLARE cursor_InterID CURSOR FOR SELECT INTERID FROM DYNAMICS..SY01500 INNER JOIN master..sysdatabases ON name = INTERID OPEN cursor_InterID FETCH NEXT FROM cursor_InterID INTO @IntercompanyID WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) SET @SQLStatement = 'UPDATE ' + @IntercompanyID + '..GL40000 SET KPBUDTRXHIST = 1 WHERE KPBUDTRXHIST = 0' EXEC (@SQLStatement) FETCH NEXT FROM cursor_InterID INTO @IntercompanyID END CLOSE cursor_InterID DEALLOCATE cursor_InterID
You will need to amend the script if your System Database is not called DYNAMICS.