Every so often when doing an upgrade, or implementing a module, for a client, we encounter errors when doing the GP Utilities database upgrade. This time round we encountered the error when implementing Fixed Asset Management (FAM) in Microsoft Dynamics GP 2015 R2.
We had previously upgraded the client from Dynamics GP 2010 R2 where they had not been using Fixed Asset Management and never had. However, despite the feature not being installed in Dynamics GP 2010 R2 and the client never knowing having used it, there were tables for Fixed Asset Management in their of the 20+ company databases; this looks like one of their previous partners had done something odd when creating these companies.
The solution in this case was to remove all of the Fixed Asset Management tables from the database. When I have done this type of thing before I have manually written scripts to do this, but have tired of doing so (the previous time I had to do this it was the HR modules.
So I wrote a simple script using a cursor which is run against the system database and which loops through all of the company databases and generates delete scripts for all of the tables for the designated module.
It also generates scripts to delete the rows from the DU tables in the system database.
There are three parameters at the top which need to be set:
- The database at the top which should be a system database
- @PRODID which is the numeric product id; for FAM this 309
- @TablePrefix which is the alpha prefix to the table names, which for FAM is FA
The parameters are not authenticated or verified in any so oyu need to make sure the product id and table prefix are correct before proceeding.
When the script is run, output it to Text which will give you a series of DROP TABLE commands you can then verify you are happy with the scripts before running them. I would strongly recommend checking the scripts and running them on a test system containing a copy of live first to ensure the result is what you require.
These fields have been highlighted in the, below, script:
/*
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).
*/
USE D16R1
GO
DECLARE @PRODID INT = 309
DECLARE @TablePrefix VARCHAR(5) = 'FA'
DECLARE @SQL_Statement VARCHAR(1000)
CREATE TABLE #Scripts(
COMMAND VARCHAR(200)
)
DECLARE
cursor_InterID CURSOR
FOR
SELECT
RTRIM(INTERID)
FROM
SY01500
UNION
SELECT DB_NAME()
OPEN cursor_InterID
DECLARE @INTERID VARCHAR(100)
FETCH NEXT FROM
cursor_InterID
INTO
@INTERID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SET @SQL_Statement = 'INSERT INTO #Scripts (COMMAND) (SELECT ''DROP TABLE ' + @INTERID + '..'' + name FROM ' + RTRIM(@INTERID) + '.sys.tables WHERE name LIKE ''' + @TablePrefix + '%'')'
EXEC (@SQL_Statement)
END
FETCH NEXT FROM
cursor_InterID
INTO
@INTERID
END
CLOSE cursor_InterID
DEALLOCATE cursor_InterID
INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DB_Upgrade WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DU000010 WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DU000020 WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DU000030 WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
GO
SELECT COMMAND + CHAR(10) + 'GO' FROM #Scripts
GO
DROP TABLE #Scripts
GO
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Thanks for this! Very helpful. Really appreciate the advice. I used it and everything went through fine. Thanks!
Vince Stefanetti
Does anyone know of the equivalent scripts to remove the Manufacturing Module tables?