This SQL script was written for a client with a large number of databases and which took a long time to update and they needed to upgrade through several versions of Microsoft Dynamics GP. The script checks for start and end times in the DB_UPGRADE table; when they match for all rows, an error is raised.
The script was scheduled using SQL Server Agent which was configured to send an email when the error was raised; this allowed him to know when part of the upgrade had completed and the next stage needed to be started.
/*
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).
*/
DECLARE @UpgradeComplete AS INT
SELECT
@UpgradeComplete = CASE WHEN COUNT(PRODID) = 0 THEN 1 ELSE 0 END
FROM
DB_UPGRADE
WHERE
start_time = stop_time
IF (@UpgradeComplete = 1)
/*
throw error because all companies upgraded
if <> 0, that means upgrade still running and nothing needs to occur
*/
RAISERROR ('GP 2016 upgrade complete; next stage needs to be started',11,1)