Pro Upgrade Instructions
Overview
These are the step-by-step instructions on upgrading a pro database for ManagerPlus.
Step 1
Download the database from customers SharePoint Database folder: https://corpioffice.sharepoint.com/sites/ProfessionalServicesAssetCenter/Shared%20Documents/Forms/AllItems.aspx?viewid=3ac59aa3%2Dc5d2%2D43df%2Daac2%2D20b763bcb34a&id=%2Fsites%2FProfessionalServicesAssetCenter%2FShared%20Documents%2FManagerPlus%2FDatabases
Step 2
Get customers company code from the admin tool and name the file using the following format:
Companycode_year, month, day.bak (ie: airstream_20210825.bak) and move it to the Backup folder
Step 3
Connect to SSMS to restore the database by right clicking the Database section in the left column and selecting Restore Database and rename it using the Company Code
Step 4
Verify the what version of ManagerPlus the database is currently on by running one of the following scripts:
SELECT * FROM Config OR SELECT * FROM Setup. If the Config command works then move forward using the Desktop Upgrade instructions. If the Setup command works follow the instructions for a Pro Upgrade
The following steps are for a PRO UPGRADE: (SELECT * FROM setup)
Step 1
Make sure the database is running the 7.37 version of ManagerPlus. If it is not you will need to run each of the following scripts in order to get the database on the correct version (example: if database is on 7.24 you will run scripts 7.25 all the way up to 7.37)
Step 2
Run the following Pro Fix Upgrade where the counts are greater than 0
/* Check and fix Schedule Linking */
SELECT COUNT(*) AS badScheduleCount FROM schedules WHERE supercedes is not null and (RESET is not null or reset2 is not null)
UPDATE schedules set supercedes = NULL, RESET = NULL, Reset2 = NULL WHERE supercedes is not null and (RESET is not null or reset2 is not null)
/* Check and fix bad Vendors */
SELECT COUNT(busType) AS badVendorCount FROM vendor WHERE busType = ''
UPDATE vendor SET [busType]= NULL WHERE [busType] = ''
/* Check and fix bad Log Details */
SELECT COUNT(*) AS badLogCount FROM logdetail WHERE (hours < 0 or hours > 1000000) or (distance < 0 or distance > 10000000)
UPDATE logdetail SET [hours] = 0 WHERE hours < 0 OR hours > 1000000
UPDATE logdetail SET [distance] = 0 WHERE distance < 0 OR distance > 10000000
Step 3
Back up the database and then restore a second copy naming it with the company code_pro (ie: airstream_pro)
Step 4
Run the DBTool
- Check the box confirming you have backed up the database. Hit next
-
Select the correct SQL Server
- Select the database and hit 'Upgrade'
You may have to get the correct database by selecting 'Change Database'. A separate box will appear with the databases to select from. Select the correct one and then continue with the upgrade.
Step 5
If there are errors when updating use the knowledge base to find the solution and complete that before trying to run the DBTool again
Step 6
Once the fix has been applied restart the DBTool and let it complete the upgrade
Step 7
After it has upgraded to 2016.04 run the following script to update the budgets, log types, and part descriptions
Remember to put the correct company code for the database
/* Check and fix budgets with NULL description after DB upgraded to Desktop*/
SELECT COUNT(*) AS BadBudgetCount FROM Budgets b WHERE b.Description IS NULL
UPDATE Budgets SET [DESCRIPTION] = 'Pro Budget' WHERE [DESCRIPTION] IS NULL
/* Check and fix log types*/
SELECT * FROM dbo.LogTypes
--Metered logs should allow resets, if not metered, it should not allow resets
UPDATE LogTypes SET Metered = 1 WHERE Allow_Reset = 1 AND Log_Type_ID <> 'Qty'
UPDATE LogTypes SET Allow_Reset = 0 WHERE Metered = 0 AND Log_Type_ID = 'Qty'
/*Update part descriptions*/
-- copy and paste company code for two different DBs. Desktop version and pro.
--This code will update part descriptions on pro non-stock parts.
SELECT * FROM companycode.dbo.parts ORDER BY companycode.dbo.parts.Part_ID
UPDATE companycode.dbo.Parts SET Description = WOP.Description FROM
(SELECT ROW_NUMBER() OVER(Partition BY [Part ID] ORDER BY [Work Order Part ID]) AS RowNumber
, [Part ID]
, Description
FROM companycode_pro.dbo.WorkOrderParts) WOP
WHERE WOP.RowNumber = 1
AND companycode.dbo.Parts.Part_ID = WOP.[Part ID] AND companycode.dbo.Parts.Description = 'Non-Stock Part' and WOP.Description is not NULL
SELECT * FROM
(SELECT ROW_NUMBER() OVER(Partition BY [Part ID] ORDER BY [Work Order Part ID]) AS RowNumber
, [Part ID]
, Description
FROM companycode_pro.dbo.WorkOrderParts) WOP2
WHERE WOP2.RowNumber = 1 and WOP2.Description is not NULL
ORDER BY WOP2.[Part ID]
SELECT * FROM companycode.dbo.parts ORDER BY companycode.dbo.parts.Part_ID
Step 8
Next run the Security_UserCreation script replacing the default IM with the correct one and open the 2016.04 version of ManagerPlus
--SELECT * FROM SecurityRoles sr
--SELECT * FROM Entity e
--SELECT * FROM ContactStatus cs
--SELECT * FROM SecurityUsers su
--SELECT * FROM Contacts c
/*User ID's*/
/*
lspoklie
mgraham
ddemarchi
eandrews
lkisan
twoo
tleichtle
bpeterson
mthacker
jskinner
*/
DECLARE @ContactKEY INT
DECLARE @UserKEY INT
DECLARE @UserID NVARCHAR(24) = 'IM UserID' --Implementation Manager ID
DECLARE @StatusKEY INT
DECLARE @StatusID NVARCHAR(24) = 'USER'
DECLARE @RoleKEY INT
DECLARE @RoleID NVARCHAR(24) = 'Administrator'
SET @StatusKEY = (SELECT Status_KEY FROM ContactStatus WHERE Status_ID = @StatusID)
SELECT @RoleKEY = sr.Role_KEY FROM SecurityRoles sr WHERE EXISTS(SELECT * FROM SecurityRoleOptions sro WHERE sro.Option_KEY = 1 AND sro.Permission = 1 AND sr.Role_KEY = sro.Role_KEY)
AND @RoleID = sr.Role_Name
IF @RoleKEY IS NULL
BEGIN
SET @RoleKEY = (SELECT TOP (1)sr.Role_KEY FROM SecurityRoles sr WHERE EXISTS(SELECT * FROM SecurityRoleOptions sro WHERE sro.Option_KEY = 1 AND sro.Permission = 1 AND sr.Role_KEY = sro.Role_KEY))
END
SELECT @ContactKEY = c.Contact_KEY FROM Contacts c WHERE c.Contact_ID = @UserID
IF @ContactKEY IS NULL
BEGIN
INSERT INTO Contacts (Contact_ID, Internal_Name, Contact_Name, Contact_Type, Status_KEY, Email_Address, Used_in_Work_Orders, Labor_Rate, Tag)
VALUES (@UserID, @UserID, @UserID, 400, @StatusKEY, CONCAT(@UserID, '@managerplus.com'), 0, 0, NULL)
SET @ContactKEY = (SELECT Contact_KEY FROM Contacts WHERE Contact_ID = @UserID)
END
INSERT INTO SecurityUsers (User_Name, Password, Contact_KEY, User_Class, Status_KEY, PWSalt, Reset_Password_On_Login, ResetKey)
SELECT @UserID, HASHBYTES('MD5', 'admin1234'), @ContactKEY, 1, @StatusKEY, NULL, 0, NULL FROM Contacts
WHERE Contact_KEY = @ContactKEY AND NOT EXISTS (SELECT * FROM SecurityUsers su WHERE su.Contact_KEY = @ContactKEY)
SET @UserKEY = (SELECT su.User_KEY FROM SecurityUsers su WHERE su.User_Name = @UserID)
INSERT INTO ContactEntity (Contact_KEY, Entity_KEY)
SELECT @ContactKEY, e.Entity_KEY FROM Entity e WHERE NOT EXISTS (SELECT * FROM ContactEntity ce WHERE ce.Contact_KEY = @ContactKEY AND ce.Entity_KEY = e.Entity_KEY)
INSERT INTO SecurityUsersEntityRoles (User_KEY, Entity_KEY, Role_KEY)
SELECT @UserKEY, e.Entity_KEY, @RoleKEY FROM Entity e WHERE NOT EXISTS (SELECT * FROM SecurityUsersEntityRoles suer WHERE suer.User_KEY = @UserKEY AND suer.Role_KEY = @RoleKEY AND suer.Entity_KEY = e.Entity_KEY)
SELECT * FROM Contacts c WHERE c.Contact_ID = @UserID
SELECT * FROM SecurityUsers su WHERE su.User_Name = @UserID
SELECT * FROM ContactEntity ce WHERE ce.Contact_KEY = @ContactKEY
SELECT * FROM SecurityUsersEntityRoles suer WHERE suer.User_KEY = @UserKEY
Step 9
In ManagerPlus under the 'Module' tab go to 'Budgets' and click on the GREEN PLUS sign to add a new budget
- Name the new budget INVENTORY MANAGEMENT and select 'OPERATIONS' from the drop down. Hit 'Save'
- Open 'Cycle Count' and click on the GREEN PLUS sign to add a new cycle count under the budget you just created. Take the count to '0', save and then close Cycle Count
Step 10
Run the cycle count if there are any parts in the table 'Inventory Received'
SELECT * FROM InventoryReceived IR
SELECT * FROM InventoryCycleCountParts ICCP
SELECT * FROM InventoryCycleCountPartsReceived ICCPR
-- Create a cycle count and close it. Do Not post.
-- Modify cycle count number below if necessary.
INSERT INTO InventoryCycleCountParts
SELECT 1 [Inventory_Cycle_Count_ID],
IR.Part_KEY, 1 [Times_Counted],
IR.Quantity AS [Current_Count],
NULL AS [Counted_By], GETDATE() AS [Date_Posted],
NULL AS [Last_Count], 2 [Last_Count_Code], IR.Cost AS [Part_Cost], 1 [Posted]
FROM InventoryReceived IR
INSERT INTO InventoryCycleCountPartsReceived
SELECT ICCP.Cycle_Count_Part_KEY, IR.Received_KEY FROM InventoryCycleCountParts ICCP
INNER JOIN InventoryReceived IR ON ICCP.Part_KEY = IR.Part_KEY
Step 11
Clean up Work Types and Part Types by running the following script:
Review Work Types and substitute other options where 'OIL CHANGE' or 'REPAIR' do not already exist
--Ensure REPAIR and OIL CHANGE exist. Otherwise replace with another option.
SELECT * FROM WorkType ORDER BY Work_Type_ID
--Move old work type to the purpose field.
UPDATE WorkOrders SET Purpose = '' WHERE Purpose IS NULL
UPDATE wo SET Purpose = wt.Work_Type_ID + '-' + wo.Purpose FROM WorkOrders wo INNER JOIN WorkType wt ON wo.Work_Type_KEY = wt.Work_Type_KEY
UPDATE WorkType SET Work_Type_ID = 'CORRECTIVE' WHERE Work_Type_ID = 'REPAIR'
UPDATE WorkType SET Work_Type_ID = 'PREVENTIVE' WHERE Work_Type_ID = 'OIL CHANGE'
--Update work types on all existing Work Orders and Schedules.
UPDATE Schedules SET Work_Type_KEY = (SELECT wt.Work_Type_KEY FROM WorkType wt WHERE wt.Work_Type_ID = 'PREVENTIVE')
UPDATE wo SET Work_Type_KEY = (SELECT wt.Work_Type_KEY FROM WorkType wt WHERE wt.Work_Type_ID = 'PREVENTIVE') FROM WorkOrders wo WHERE wo.Schedule_KEY IS NOT NULL
UPDATE wo SET Work_Type_KEY = (SELECT wt.Work_Type_KEY FROM WorkType wt WHERE wt.Work_Type_ID = 'CORRECTIVE') FROM WorkOrders wo WHERE wo.Schedule_KEY IS NULL
--Remove bad work types.
DELETE wtcf FROM WorkTypeCustomFields wtcf INNER JOIN WorkType wt ON wtcf.Work_Type_KEY = wt.Work_Type_KEY LEFT JOIN WorkOrders wo ON wt.Work_Type_KEY = wo.Work_Type_KEY WHERE wo.Work_Order_KEY IS NULL
DELETE wt FROM WorkType wt LEFT JOIN WorkOrders wo ON wt.Work_Type_KEY = wo.Work_Type_KEY WHERE wo.Work_Order_KEY IS NULL
Step 12
Clean up 'UNKNOWN' part type. Where parts were added ad-hoc in Pro, set them inactive and rename 'UNKNOWN' to 'NON_STOCK'. Then delete type 'UNKOWN'
UPDATE parts SET Status_KEY = (SELECT Status_KEY FROM PartStatus WHERE Status_ID = 'INACTIVE') WHERE Type_KEY = (SELECT Type_KEY FROM PartType WHERE Type_ID = 'UNKNOWN')
UPDATE iwp SET Track_Levels = 0, Listed = 0 FROM InventoryWarehouseParts iwp INNER JOIN Parts p ON iwp.Part_KEY = p.Part_KEY WHERE Type_KEY = (SELECT Type_KEY FROM PartType WHERE Type_ID = 'UNKNOWN')
INSERT INTO PartType (Type_ID) VALUES (N'NON-STOCK')
UPDATE Parts SET Type_KEY = (SELECT Type_KEY FROM PartType WHERE Type_ID = 'NON-STOCK') WHERE Type_KEY = (SELECT Type_KEY FROM PartType WHERE Type_ID = 'UNKNOWN')
DELETE FROM PartType WHERE Type_ID = 'UNKNOWN'
DELETE FROM PartStatus WHERE Status_ID = 'UNKNOWN'
Step 13
Continue with the Universal Upgrade script. Make sure you add the correct Company Code
UPDATE Config SET Base_Web_URL = NULL
UPDATE Company SET UTCDATE = 1
UPDATE configsecurity SET USE_LDAP_EasyLinks = 0
UPDATE configsecurity
SET USE_LDAP_ManagerPlus = 0 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FeatureToggles]( [Feature_Name] [nvarchar](100) NOT NULL, [Enabled] [bit] NOT NULL, [Reversible] [bit] NOT NULL, CONSTRAINT [PK_FeatureToggle] PRIMARY KEY CLUSTERED ( [Feature_Name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FeatureToggles]
ADD CONSTRAINT [DF_FeatureToggles_Reversible]
DEFAULT ((1)) FOR [Reversible]
GO
INSERT INTO FeatureToggles SELECT 'ManagerPlus2',0,0
INSERT INTO FeatureToggles SELECT 'API',0,1
INSERT INTO FeatureToggles SELECT 'Connectors',0,1 UPDATE Schedules
SET Purpose = Schedule_ID
WHERE Purpose IS NULL OR Purpose = ''
--update company code for these two lines
ALTER DATABASE CompanyCode SET AUTO_CLOSE OFF WITH NO_WAIT
ALTER DATABASE CompanyCode SET RECOVERY SIMPLE
Step 14
Verify that both Company Name and Entity are NOT blank
SELECT * FROM Company
SELECT * FROM Entity
*if one of those fields are blank run this fix script
UPDATE c SET Company_Name = e.Entity_Name
FROM Company c INNER JOIN Entity e ON c.Company_KEY = e.Company_KEY
Step 15
Verify what inventory method the customer is on. The customer should be on inventory method 3. If not send a message to the IM and let them know.
1-Last Cost 2- Rolling Cost 3- FIFO (First In First Out) 4- LIFO (Last In First Out)
SELECT * FROM ConfigInventory ci
Continue with the rest of the script
SELECT * FROM WorkPriority wp
UPDATE SU SET SU.User_Class = 1 FROM SecurityUsers SU WHERE SU.User_Class = 2
UPDATE configworkorders SET default_note_type = NULL
UPDATE ca SET Address_Type_KEY = 3--, [Default] = 1
FROM ContactAddresses ca
LEFT JOIN Contacts c ON ca.Contact_KEY = c.Contact_KEY
WHERE Contact_Type = 200
**RUN THIS PART FIRST AND THEN RUN THE ABOVE SCRIPT. IF THE NUMBERS THAT COME BACK FROM QUERY ARE THE SAME, REMOVE THE 2 DASHES BEHIND THE #3 IN THE TOP SCRIPT AND RE-RUN IT.**
SELECT COUNT(DISTINCT(c.Contact_KEY)) FROM ContactAddresses ca
LEFT JOIN Contacts c ON ca.Contact_KEY = c.Contact_KEY
WHERE Contact_Type = 200