How to See the Current Status of a Dynamics GP Upgrade or Service Pack

UPDATE: See updated version here.

I've referenced before that we have a customer with over 80 GP companies; they are the mother of many script inventions for me as I have no desire to manually do anything for 80 databases. Well, now they are at 90.

Needless to say, one of the many challenges with such a large number of companies is keeping track of where the database processing is during an upgrade or service pack. It would also be nice to know how long the thing is going to take.

Here is a handy little script I created to help keep track of this:

Edit 8/20/2014: Fixed a couple letter cases to work properly for Binary SQL sorting
Edit 10/15/2014: See updated script here

DECLARE @NewVersion int
DECLARE @NewBuild int

SET @NewVersion = 11    -- Change this to the major version number you are upgrading TO
SET @NewBuild = 1752    -- Change this to the build number you are upgrading TO

IF object_id('tempdb..#Completed_List') IS NOT NULL
BEGIN
   DROP TABLE #Completed_List
END

SELECT * 
INTO #Completed_List
FROM (
    SELECT
        Completed_Company = RTRIM(db_name),
        Started_At = (SELECT start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0),
        Ended_At = CASE WHEN (MIN(db_verOldMajor) <> @NewVersion OR MIN(db_verOldBuild) <> @NewBuild OR MIN(db_verOldMajor) <> MIN(db_verMajor) OR MIN(db_verOldBuild) <> MIN(db_verBuild)) AND MAX(db_status) <> 0 THEN GETDATE() ELSE MAX(stop_time) END,
        Run_Time = 
            CASE WHEN (MIN(db_verOldMajor) <> @NewVersion OR MIN(db_verOldBuild) <> @NewBuild OR MIN(db_verOldMajor) <> MIN(db_verMajor) OR MIN(db_verOldBuild) <> MIN(db_verBuild)) AND MAX(db_status) <> 0 THEN
                GETDATE()-(SELECT start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0)
            ELSE
                MAX(stop_time)-(SELECT start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0)
            END,
        Upgrading = CASE WHEN MAX(db_status) <> 0 THEN 1 ELSE 0 END
    FROM
        DB_Upgrade DBU
    WHERE
        EXISTS(
            select *
            from DB_Upgrade where
            db_verMajor = @NewVersion
            and db_verBuild = @NewBuild 
            AND PRODID =  0
            AND db_name <> 'DYNAMICS'
            and db_name = DBU.db_name
        )
    GROUP BY db_name
) a

SELECT 
    Currently_Upgrading = ISNULL((select TOP 1 db_name from DB_Upgrade where ((db_verMajor = @NewVersion AND db_verBuild = @NewBuild) AND (db_verOldMajor <> db_verMajor OR db_verOldBuild <> db_verBuild)) and PRODID = 0 and db_name <> 'DYNAMICS' order by start_time desc),
                                (SELECT TOP 1 db_name FROM DB_Upgrade WHERE db_status <> 0)),
    Not_Upgraded = (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS'),
    Upgraded = (select COUNT(*) from DB_Upgrade WHERE (db_verMajor = @NewVersion AND db_verBuild = @NewBuild AND db_verOldMajor = db_verMajor AND db_verOldBuild = db_verBuild)  and PRODID = 0 and db_name <> 'DYNAMICS'),
    Average_Time_Per_DB = 
        CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 'h ' +
        CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 'm ' +
        CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 's '
    ,
    Elapsed_Time =
        CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 'h ' +
        CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 'm ' +
        CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 's '
    ,
    Estimated_Remaining_Time = 
        CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 'h ' +
        CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 'm ' +
        CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 's '
                                
SELECT
        Completed_Company = CASE WHEN Upgrading = 1 THEN Completed_Company + ' - Upgrading' ELSE Completed_Company END,
        Started_At,
        Ended_At,
        Run_Time = 
            CONVERT(varchar(4),DATEPART(HOUR,(Run_Time))) + 'h ' +
            CONVERT(varchar(4),DATEPART(MINUTE,(Run_Time))) + 'm ' +
            CONVERT(varchar(4),DATEPART(SECOND,(Run_Time))) + 's '
FROM
    #Completed_List
ORDER BY 
    Upgrading DESC, 
    Ended_At DESC

IF object_id('tempdb..#Completed_List') IS NOT NULL
BEGIN
   DROP TABLE #Completed_List
END