This is a SQL script that will solve the problem associated with on-premise accounts that still have some ntext fields in their database from older versions of CRM.
The ntext fields do not allow some of the actions that are used in the views of CRM, DISTINCT being an example of one of these commands. This will stop the views from being created and cause problems within the install.
This script does not, however, replace all ntext fields on the database with nvarchar. This only replaces the ntext fields that were included in a uncustomised (default) install of CRM so that any custom columns with big blocks of information do not get removed or trimmed. This should still solve the problems with the standard views as no vanilla views will be looking at the custom columns.
The first part of the script changes all of the ntext fields to nvarchar and the second part rebuilds all of the views metadata in the database so that the views are rebuilt with the new field types.
This script changes a lot of rows in the database so it may be advisable to take a back-up of the database before it is ran in the event that some information is lost.
Once this script is executed on the database the CRM logs should be saved into a folder and then deleted from the main logs file before the user accesses CRM the first time after the change to make sure that there are no errors in the logs due to the recreation of the views.
-------------------------------------------
-- you may need to change 'CRM' to the correct name of the database
USE CRM
GO
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
----------------------------
