Are you tired of troubleshooting the application after an upgrade?
Are you tired of unexpected screen and data errors after a conversion?Were you wondering what your customer is going to think when they start testing their new CRM version?
When upgrading CRM you need to perform a step upgrade from one version to the next version.
For example, if starting at version 7.2, the conversion should be completed from 7.2 -> 7.3 SP3 -> 2017 R3 -> 2018 R2.
After completing one version you must log as Administrator and run metadata refresh before upgrading to the next version. This is required to update the database tables correctly and prevent missing object and tables.
Because of issues, that in version 2018 R3 there is Enhanced error handling (0-170012-ENH). Sage CRM Setup has been improved to verify the integrity of the MailChimp Integration-related database tables and views during upgrading. If an issue is found, Sage CRM Setup displays an error message.
Upgrade scripts can be found under the install C:\Program Files (x86)\Sage\CRM\CRM2018R1\Setup\SQLServer\Upgrade.
You can find the Release Notes and Installation and Upgrade Guides on the Help Centre (http://help.sagecrm.com) which will document all known issues.
And of course, upgrades need to be tested before going live.
----- Fine tune columns datatype in case they are missed during upgrade.
CREATE TABLE #NtextToNvarchar (table_name NVARCHAR, column_name NVARCHAR)
DECLARE @table_name NVARCHAR(max)
DECLARE @column_name NVARCHAR(max)
DECLARE @sql NVARCHAR(max)
--Declaring the cursor to run the query for each ntext column
DECLARE db_cursor CURSOR FOR
SELECT t.name AS table_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name in (
SELECT column_name
FROM information_schema.columns
where data_type = 'ntext' and column_name in ('acse_altvalue', 'acse_value', 'AdFi_ContentFields', 'AdFi_SQLText',
'Arti_ConflictResolverScript', 'Arti_CustomHFilterSQL', 'Arti_Script',
'lCall_comment', 'CLT_ReasonFinished', 'Case_ProblemNote', 'Case_ProgressNote',
'Case_SolutionNote', 'Case_ProblemNote', 'Case_SolutionNote', 'Comm_BCC',
'Comm_CC', 'Comm_Email', 'Comm_From', 'Comm_Note', 'Comm_ReplyTo', 'Comm_TO',
'Cmp_Description', 'Capt_CS', 'Capt_DE', 'Capt_DU', 'Capt_ES', 'Capt_FR',
'Capt_JP', 'Capt_UK', 'Capt_US', 'Colp_ssViewField', 'emse_fromchannelrestriction',
'emse_fromuserrestriction', 'emse_replytochannelrestrict', 'emse_replytouserrestriction',
'GriP_CreateScript', 'CPag_Html_CS', 'CPag_Html_DE', 'CPag_Html_DU', 'CPag_Html_ES',
'CPag_Html_FR', 'CPag_Html_JP', 'CPag_Html_UK', 'CPag_Html_US', 'CPag_Script',
'Repo_Description', 'ReSe_QueryFields', 'ReSe_SQLText', 'CObj_CreateScript',
'Cobj_CustomContent', 'CObj_Properties', 'SeaP_CreateScript', 'SeaP_OnChangeScript',
'SeaP_ValidateScript', 'CScr_Script', 'Parm_Value', 'Tabs_WhereSQL', 'CuVi_ViewScript',
'DDFld_ChildrenIDs', 'EmTe_BCC', 'EmTe_CC', 'EmTe_Comm_Email', 'EmTe_Comm_From',
'EmTe_Comm_Note', 'EmTe_Comm_ReplyTo', 'EmTe_To', 'ERPI_RTDSchema', 'ERPI_SyncSchema',
'GGS_MetadataDocument', 'GGS_SchemaDocument', 'Lead_Details', 'Lead_ReasonNotInterested',
'Lead_Details', 'Lead_ProgressNote', 'Lead_ReasonNotInterested', 'Libr_Note',
'LPGad_DataBinding', 'LPGad_Description', 'LPGad_LayoutXml', 'LPGad_Type',
'LPLayout_Description', 'LPLayout_LayoutXml', 'LPLayout_Type', 'mesg_body',
'view_details', 'view_whereClause', 'Note_Note', 'Oppo_Note', 'Oppo_ProgressNote',
'OrIt_SynchMessage', 'Orde_billaddress', 'Orde_shipaddress', 'Orde_SynchMessage',
'prli_Description', 'QuIt_SynchMessage', 'Quot_billaddress', 'Quot_shipaddress',
'quot_SynchMessage', 'Soln_SolutionDetails', 'Soln_ProgressNote', 'TLSD_Fields',
'TLSD_WhereClause', 'TLog_Message', 'TLog_WhereClause', 'TList_Description',
'TList_SQL', 'Team_Note', 'UsrA_UserAgent', 'User_Per_InfoAdmin',
'User_RecentList', 'USet_Value', 'USetDef_Value', 'wait_commdetails',
'wait_contactedtext', 'WaIt_Details', 'wait_introtext', 'WkAc_Attributes',
'WkAc_EmailBCC', 'WkAc_EmailBody', 'WkAc_EmailCC', 'WkAc_EmailSubject',
'WkAc_EmailTo', 'WkAc_Value', 'WkRl_JavaScript', 'WkRl_WhereClause',
'WkSt_Description')
group by column_name)
ORDER BY table_name, column_name;
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @table_name , @column_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'alter table ['+ @table_name +'] alter column ['+ @column_name +'] nvarchar(max)
update ['+ @table_name +'] set ['+ @column_name +'] = ['+ @column_name +']'
EXEC (@sql)
FETCH NEXT FROM db_cursor INTO @table_name , @column_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- Fix views metadata after update
DECLARE @viewName AS VARCHAR(255)
DECLARE incorrectViews CURSOR FAST_FORWARD FOR
SELECT name FROM sysobjects
WHERE UPPER(xtype) = 'V'
OPEN incorrectViews
FETCH NEXT FROM incorrectViews INTO @viewName
WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN TRY
IF (UPPER(@viewName) <> UPPER('vSystemTablesCatalog')) AND (UPPER(@viewName) <> UPPER('vSystemViewsCatalog'))
BEGIN
EXEC sp_refreshview @viewName
END
END TRY
BEGIN CATCH
-- DO NOTHING
END CATCH
FETCH NEXT FROM incorrectViews INTO @viewName
END
CLOSE incorrectViews
DEALLOCATE incorrectViews
go
