Issues with Collation

Apr 1, 2009 at 2:26 AM
We installed CRM with "Latin1_General_CI_AI" collation but over a period of time we see that the data is Assent-sensitive.
And for the City,Country and StateorProvince columns in the LeadAddressBase table the value of Quebec and 'QuébecCanada' the Accelerator is failing while processing Geography cube.

Following is the Query which is failing haivng values Quebec and 'QuébecCanada' and collation "Latin1_General_CI_AI"

SELECT DISTINCT

ISNULL(city, 'NA_') + ISNULL(stateorprovince, 'NA_') + ISNULL(country, 'NA_') AS CityCode,

ISNULL(city, 'NA_') AS CityDesc,

stateorprovince + country AS StateCode,

stateorprovince AS StateDesc,

country AS CountryCode,

country AS CountryDesc

 

FROM FilteredLeadAddress


To complete the Cube Process

1. Took the backup of the orgname_MSCRM database.
2. Ran the Following script to change the Collation of the

Use orgname_MSCRM

Alter Table LeadAddressBase
Alter Column Country nvarchar(50) COLLATE Latin1_General_CI_AS
GO
Alter Table LeadAddressBase
Alter Column City nvarchar(50) COLLATE Latin1_General_CI_AS
Go
Alter Table LeadAddressBase
Alter Column stateorprovince nvarchar(50) COLLATE Latin1_General_CI_AS

3. Expand View under orgname_MSCRM database --> Select LeadAddress View --> Right click on Script View as --> ALTER to --> New Query Editor Window
then ran the query.

4. Did steps 3 for FilteredLeadAddress View also.

5. Tested the following query.

SELECT DISTINCT
ISNULL(city, 'NA_') + ISNULL(stateorprovince, 'NA_') + ISNULL(country, 'NA_') AS CityCode,
ISNULL(city, 'NA_') AS CityDesc,
stateorprovince + country AS StateCode,
stateorprovince AS StateDesc,
country AS CountryCode,
country AS CountryDesc

FROM FilteredLeadAddress

6. After that the Geography process was successful.


So my question is , Is there an easy way to convert the Whole Collation to "Latin1_General_CI_AS" for all the Objects?

If Not , if I leave the database Collation as "Latin1_General_CI_AI" and change only few columns collation to "Latin1_General_CI_AS" is this supported in CRM?

Regards,
Daniel.





Apr 30, 2009 at 3:30 AM
Has anyone else reported the same issue? If so does anyone have a solution?
May 28, 2009 at 12:47 PM
Edited May 28, 2009 at 12:49 PM

Hi Daniel,

you can run this query in SQL (it is unsupported and you should test it at a dummy organisation, but it worked fine for me)

Just stop the async service and SQL agent service, and alter the script (test_MSCRM should be the organisation database)   where before running this script.

--change collation

use test_MSCRM

declare @target_collation sysname

drop function [dbo].[fn_GetFormatStrings]

exec [p_SetDbCollation] 'test_MSCRM', 'Latin1_General_CI_AI'
go

create function [dbo].[fn_GetFormatStrings]()
returns @FormatStrings table (
 DateFormat nvarchar(255),
 TimeFormat nvarchar(255),
 NumberLanguageCode nvarchar(10),
 CalendarType nvarchar(50),
 NumberFormat_0_Precision nvarchar(255),
 NumberFormat_1_Precision nvarchar(255),
 NumberFormat_2_Precision nvarchar(255),
 NumberFormat_3_Precision nvarchar(255),
 NumberFormat_4_Precision nvarchar(255),
 NumberFormat_5_Precision nvarchar(255),
 CurrencyFormat_0_Precision nvarchar(255),
 CurrencyFormat_1_Precision nvarchar(255),
 CurrencyFormat_2_Precision nvarchar(255),
 CurrencyFormat_3_Precision nvarchar(255),
 CurrencyFormat_4_Precision nvarchar(255),
 CurrencyFormat_5_Precision nvarchar(255)
)
as
begin
 declare @CurrencySymbol nvarchar(10)
 declare @CurrencyFormatCode int
 declare @NumberLocale int
 declare @CalendarType nvarchar(50)
 declare @NumberGroupFormat nvarchar(50)
 declare @NegativeFormatCode int
 declare @NegativeCurrencyFormatCode int
 declare @DateFormatString nvarchar(255)
 declare @TimeFormatString nvarchar(255)
 declare @NumberLanguageCode nvarchar(10)
 
 declare @s1 nvarchar(255)
 declare @s2 nvarchar(255)
 declare @s3 nvarchar(255)
 declare @s4 nvarchar(255)
 declare @s5 nvarchar(255)
 declare @s6 nvarchar(255)
 declare @s7 nvarchar(255)
 declare @s8 nvarchar(255)
 declare @s9 nvarchar(255)
 declare @s10 nvarchar(255)
 declare @s11 nvarchar(255)
 declare @s12 nvarchar(255)

 select top 1
  @CurrencySymbol =
  case o.currencydisplayoption
   when 0 then t.currencysymbol
   when 1 then t.isocurrencycode
   else t.currencysymbol
  end
 from
  FilteredOrganization AS o inner join
  FilteredTransactionCurrency AS t on o.basecurrencyid = t.transactioncurrencyid

 select top 1
  @CurrencyFormatCode = currencyformatcode,
  @NumberLocale = localeid,
  @CalendarType =
  case calendartype
   when 1 then 'Japanese'
   when 2 then 'Korea'
   when 3 then 'Taiwan'
   when 4 then 'Gregorian US English'
   when 5 then 'Gregorian Arabic'
   when 6 then 'Gregorian Middle East French'
   when 7 then 'Gregorian Transliterated English'
   when 8 then 'Gregorian Transliterated French'
   else 'Gregorian'
  end,
  @NumberGroupFormat = numbergroupformat,
  @NegativeFormatCode = negativeformatcode,
  @NegativeCurrencyFormatCode = negativecurrencyformatcode,
  @DateFormatString = replace(dateformatstring, '/', '\' + dateseparator),
  @TimeFormatString = replace(timeformatstring, ':', '\' + timeseparator)
 from
  FilteredUserSettings
 where
  systemuserid = dbo.fn_FindUserGuid()

 set @NumberLanguageCode =
  case @NumberLocale
   when 1025 then 'ar-SA'
   when 1026 then 'bg-BG'
   when 1027 then 'ca-ES'
   when 1028 then 'zh-TW'
   when 1029 then 'cs-CZ'
   when 1030 then 'da-DK'
   when 1031 then 'de-DE'
   when 1032 then 'el-GR'
   when 1033 then 'en-US'
   when 1035 then 'fi-FI'
   when 1036 then 'fr-FR'
   when 1037 then 'he-IL'
   when 1038 then 'hu-HU'
   when 1039 then 'is-IS'
   when 1040 then 'it-IT'
   when 1041 then 'ja-JP'
   when 1042 then 'ko-KR'
   when 1043 then 'nl-NL'
   when 1044 then 'nb-NO'
   when 1045 then 'pl-PL'
   when 1046 then 'pt-BR'
   when 1047 then 'rm-CH'
   when 1048 then 'ro-RO'
   when 1049 then 'ru-RU'
   when 1050 then 'hr-HR'
   when 1051 then 'sk-SK'
   when 1052 then 'sq-AL'
   when 1053 then 'sv-SE'
   when 1054 then 'th-TH'
   when 1055 then 'tr-TR'
   when 1056 then 'ur-PK'
   when 1057 then 'id-ID'
   when 1058 then 'uk-UA'
   when 1059 then 'be-BY'
   when 1060 then 'sl-SI'
   when 1061 then 'et-EE'
   when 1062 then 'lv-LV'
   when 1063 then 'lt-LT'
   when 1065 then 'fa-IR'
   when 1066 then 'vi-VN'
   when 1067 then 'hy-AM'
   when 1068 then 'az-Latn-AZ'
   when 1069 then 'eu-ES'
   when 1071 then 'mk-MK'
   when 1074 then 'tn-ZA'
   when 1076 then 'xh-ZA'
   when 1077 then 'zu-ZA'
   when 1078 then 'af-ZA'
   when 1079 then 'ka-GE'
   when 1080 then 'fo-FO'
   when 1081 then 'hi-IN'
   when 1082 then 'mt-MT'
   when 1083 then 'se-NO'
   when 1086 then 'ms-MY'
   when 1087 then 'kk-KZ'
   when 1088 then 'ky-KG'
   when 1089 then 'sw-KE'
   when 1091 then 'uz-Latn-UZ'
   when 1092 then 'tt-RU'
   when 1094 then 'pa-IN'
   when 1095 then 'gu-IN'
   when 1097 then 'ta-IN'
   when 1098 then 'te-IN'
   when 1099 then 'kn-IN'
   when 1102 then 'mr-IN'
   when 1103 then 'sa-IN'
   when 1104 then 'mn-MN'
   when 1106 then 'cy-GB'
   when 1110 then 'gl-ES'
   when 1111 then 'kok-IN'
   when 1114 then 'syr-SY'
   when 1122 then 'fy-NL'
   when 1124 then 'fil-PH'
   when 1125 then 'dv-MV'
   when 1131 then 'quz-BO'
   when 1132 then 'ns-ZA'
   when 1134 then 'lb-LU'
   when 1146 then 'arn-CL'
   when 1148 then 'moh-CA'
   when 1153 then 'mi-NZ'
   when 2049 then 'ar-IQ'
   when 2052 then 'zh-CN'
   when 2055 then 'de-CH'
   when 2057 then 'en-GB'
   when 2058 then 'es-MX'
   when 2060 then 'fr-BE'
   when 2064 then 'it-CH'
   when 2067 then 'nl-BE'
   when 2068 then 'nn-NO'
   when 2070 then 'pt-PT'
   when 2074 then 'sr-Latn-CS'
   when 2077 then 'sv-FI'
   when 2092 then 'az-Cyrl-AZ'
   when 2107 then 'se-SE'
   when 2108 then 'ga-IE'
   when 2110 then 'ms-BN'
   when 2115 then 'uz-Cyrl-UZ'
   when 2141 then 'iu-Latn-CA'
   when 2155 then 'quz-EC'
   when 3073 then 'ar-EG'
   when 3076 then 'zh-HK'
   when 3079 then 'de-AT'
   when 3081 then 'en-AU'
   when 3082 then 'es-ES'
   when 3084 then 'fr-CA'
   when 3098 then 'sr-Cyrl-CS'
   when 3131 then 'se-FI'
   when 3179 then 'quz-PE'
   when 4097 then 'ar-LY'
   when 4100 then 'zh-SG'
   when 4103 then 'de-LU'
   when 4105 then 'en-CA'
   when 4106 then 'es-GT'
   when 4108 then 'fr-CH'
   when 4122 then 'hr-BA'
   when 4155 then 'smj-NO'
   when 5121 then 'ar-DZ'
   when 5124 then 'zh-MO'
   when 5127 then 'de-LI'
   when 5129 then 'en-NZ'
   when 5130 then 'es-CR'
   when 5132 then 'fr-LU'
   when 5146 then 'bs-Latn-BA'
   when 5179 then 'smj-SE'
   when 6145 then 'ar-MA'
   when 6153 then 'en-IE'
   when 6154 then 'es-PA'
   when 6156 then 'fr-MC'
   when 6170 then 'sr-Latn-BA'
   when 6203 then 'sma-NO'
   when 7169 then 'ar-TN'
   when 7177 then 'en-ZA'
   when 7178 then 'es-DO'
   when 7194 then 'sr-Cyrl-BA'
   when 7227 then 'sma-SE'
   when 8193 then 'ar-OM'
   when 8201 then 'en-JM'
   when 8202 then 'es-VE'
   when 8218 then 'bs-Cyrl-BA'
   when 8251 then 'sms-FI'
   when 9217 then 'ar-YE'
   when 9225 then 'en-029'
   when 9226 then 'es-CO'
   when 9275 then 'smn-FI'
   when 10241 then 'ar-SY'
   when 10249 then 'en-BZ'
   when 10250 then 'es-PE'
   when 11265 then 'ar-JO'
   when 11273 then 'en-TT'
   when 11274 then 'es-AR'
   when 12289 then 'ar-LB'
   when 12297 then 'en-ZW'
   when 12298 then 'es-EC'
   when 13313 then 'ar-KW'
   when 13321 then 'en-PH'
   when 13322 then 'es-CL'
   when 14337 then 'ar-AE'
   when 14346 then 'es-UY'
   when 15361 then 'ar-BH'
   when 15370 then 'es-PY'
   when 16385 then 'ar-QA'
   when 16394 then 'es-BO'
   when 17418 then 'es-SV'
   when 18442 then 'es-HN'
   when 19466 then 'es-NI'
   when 20490 then 'es-PR'
   else 'en-US'
  end

 select @s1 = dbo.fn_GetNumberFormatString(0, @NumberGroupFormat, @NegativeFormatCode, 0, default, default)
 select @s2 = dbo.fn_GetNumberFormatString(1, @NumberGroupFormat, @NegativeFormatCode, 0, default, default)
 select @s3 = dbo.fn_GetNumberFormatString(2, @NumberGroupFormat, @NegativeFormatCode, 0, default, default)
 select @s4 = dbo.fn_GetNumberFormatString(3, @NumberGroupFormat, @NegativeFormatCode, 0, default, default)
 select @s5 = dbo.fn_GetNumberFormatString(4, @NumberGroupFormat, @NegativeFormatCode, 0, default, default)
 select @s6 = dbo.fn_GetNumberFormatString(5, @NumberGroupFormat, @NegativeFormatCode, 0, default, default)
 
 select @s7 = dbo.fn_GetNumberFormatString(0, @NumberGroupFormat, @NegativeCurrencyFormatCode, 1, @CurrencySymbol, @CurrencyFormatCode)
 select @s8 = dbo.fn_GetNumberFormatString(1, @NumberGroupFormat, @NegativeCurrencyFormatCode, 1, @CurrencySymbol, @CurrencyFormatCode)
 select @s9 = dbo.fn_GetNumberFormatString(2, @NumberGroupFormat, @NegativeCurrencyFormatCode, 1, @CurrencySymbol, @CurrencyFormatCode)
 select @s10 = dbo.fn_GetNumberFormatString(3, @NumberGroupFormat, @NegativeCurrencyFormatCode, 1, @CurrencySymbol, @CurrencyFormatCode)
 select @s11 = dbo.fn_GetNumberFormatString(4, @NumberGroupFormat, @NegativeCurrencyFormatCode, 1, @CurrencySymbol, @CurrencyFormatCode)
 select @s12 = dbo.fn_GetNumberFormatString(5, @NumberGroupFormat, @NegativeCurrencyFormatCode, 1, @CurrencySymbol, @CurrencyFormatCode)

 insert
  @FormatStrings
 values(
  @DateFormatString,
  @TimeFormatString,
  @NumberLanguageCode,
  @CalendarType,
  @s1,
  @s2,
  @s3,
  @s4,
  @s5,
  @s6,
  @s7,
  @s8,
  @s9,
  @s10,
  @s11,
  @s12
 )

 return
end
go

declare @target_collation sysname
set @target_collation = 'Latin1_General_CI_AI'


-- a table of 'constraint drop' commands
create table #keydrops
(
 id int identity primary key, -- orders the rows
 sql nvarchar(4000)    -- contains the command text
)
 
-- a table of 'constraint add' commands
-- (must be calculated before the constraints are dropped)
create table #keymakes
(
 id int identity primary key, -- orders the rows
 sql nvarchar(4000),    -- contains the command text
 ord int       -- groups the commands
)

-- a table of 'alter table alter column' commands
create table #tablechanges
(
 id int identity primary key, -- orders the rows
 sql nvarchar(4000)    -- contains the command text
)

declare @sql nvarchar(4000)
declare @column nvarchar(131) -- 128+3
declare @i int

-- Step 1 generate drop and create index statements
declare @tablename sysname
declare @indexname sysname
declare @indid int
declare @is_unique_index nvarchar(16)
declare index_cursor cursor for
select distinct sysobjects.name, sysobjects.uid, sysindexes.name, sysindexes.indid,
 case
  when sysindexes.status & 2 = 2 then 'UNIQUE '
  else ''
 end
from sysindexes
 join sysindexkeys on (sysindexes.id = sysindexkeys.id
   and sysindexes.indid = sysindexkeys.indid)
 join sysobjects on (sysobjects.id = sysindexes.id)
 join syscolumns on (sysindexkeys.colid = syscolumns.colid and sysobjects.id = syscolumns.id)
 join systypes on (systypes.xtype = syscolumns.xtype and systypes.xtype = systypes.xusertype)
where
 sysobjects.xtype = 'U'
 and INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 0
 and sysindexes.name not in
  (select name from sysobjects where xtype = 'PK' or xtype = 'F ' or xtype = 'UQ')
 and (systypes.name = 'nvarchar' or systypes.name = 'nchar')
 and syscolumns.collation is not null
 and syscolumns.collation != @target_collation

declare @tableschemaid int
declare @metadataSchemaId int
set @metadataSchemaId = CONVERT(int, OBJECTPROPERTYEX(OBJECT_ID(N'MetadataSchema.Attribute'),'SchemaId'))

open index_cursor
fetch next from index_cursor into @tablename, @tableschemaid, @indexname, @indid, @is_unique_index
while (@@fetch_status = 0)
begin
 if (@tableschemaid = @metadataSchemaId)
 begin
  set @tablename = N'MetadataSchema.' + @tablename
 end

 insert into #keydrops (sql) values('DROP INDEX ' + @tablename + '.' + @indexname)
 
 select @sql = N'CREATE ' + @is_unique_index + 'INDEX ' + @indexname + N' ON ' + @tablename + N'('

   select @column = index_col(@tablename, @indid, 1)
 set @sql = @sql + @column

    select @i = 2
   select @column = index_col(@tablename, @indid, @i)
    while (@column is not null)
    begin
  set @sql = @sql + ',' + @column
        select @i = @i + 1
     select @column = index_col(@tablename, @indid, @i)
    end   
 set @sql = @sql + N')'
 insert into #keymakes (sql, ord) values(@sql, 1)

    fetch next from index_cursor into @tablename, @tableschemaid, @indexname, @indid, @is_unique_index
end
close index_cursor
deallocate index_cursor

-- Step 2 generate pk drop and pk create statement
declare @pkname sysname
declare pk_cursor cursor for
select distinct parent.name, sysobjects.name, sysindexes.indid
 from sysobjects
 join sysobjects as parent on (parent.id = sysobjects.parent_obj)
 join sysindexes on (sysobjects.name = sysindexes.name)
 join sysindexkeys on (sysindexes.id = sysindexkeys.id
   and sysindexes.indid = sysindexkeys.indid)
 join syscolumns on (sysindexkeys.colid = syscolumns.colid and parent.id = syscolumns.id)
 join systypes on (systypes.xtype = syscolumns.xtype and systypes.xtype = systypes.xusertype)
where
 sysobjects.xtype = 'PK'
 and parent.xtype = 'U' 
 and (systypes.name = 'nvarchar' or systypes.name = 'nchar')
 and syscolumns.collation is not null
 and syscolumns.collation != @target_collation
open pk_cursor
fetch next from pk_cursor into @tablename, @pkname, @indid
while (@@fetch_status = 0)
begin
 insert into #keydrops (sql) values('ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @pkname)

 select @sql = N'ALTER TABLE ' + @tablename + N' ADD CONSTRAINT ' + @pkname + ' PRIMARY KEY('
   select @column = index_col(@tablename, @indid, 1)
 set @sql = @sql + @column

    select @i = 2
   select @column = index_col(@tablename, @indid, @i)
    while (@column is not null)
    begin
  set @sql = @sql + ',' + @column
        select @i = @i + 1
     select @column = index_col(@tablename, @indid, @i)
    end   
 set @sql = @sql + N')'
 insert into #keymakes (sql, ord) values(@sql, 2)

    fetch next from pk_cursor into @tablename, @pkname, @indid
end
close pk_cursor
deallocate pk_cursor

-- Step 3 generate UNIQUE CONSTRAINT drop and UNIQUE CONSTRAINT create statement
declare @ucname sysname
declare uc_cursor cursor for
select distinct parent.name, sysobjects.name, sysindexes.indid
 from sysobjects
 join sysobjects as parent on (parent.id = sysobjects.parent_obj)
 join sysindexes on (sysobjects.name = sysindexes.name)
 join sysindexkeys on (sysindexes.id = sysindexkeys.id
   and sysindexes.indid = sysindexkeys.indid)
 join syscolumns on (sysindexkeys.colid = syscolumns.colid and parent.id = syscolumns.id)
 join systypes on (systypes.xtype = syscolumns.xtype and systypes.xtype = systypes.xusertype)
where
 sysobjects.xtype = 'UQ'
 and (systypes.name = 'nvarchar' or systypes.name = 'nchar')
 and syscolumns.collation is not null
 and syscolumns.collation != @target_collation
open uc_cursor
fetch next from uc_cursor into @tablename, @ucname, @indid
while (@@fetch_status = 0)
begin
    insert into #keydrops (sql) values('ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @ucname)

    select @sql = N'ALTER TABLE ' + @tablename + ' ADD CONSTRAINT ' + @ucname + N' UNIQUE ('       

   select @column = index_col(@tablename, @indid, 1)
 set @sql = @sql + @column

    select @i = 2
   select @column = index_col(@tablename, @indid, @i)
    while (@column is not null)
    begin
  set @sql = @sql + ',' + @column
        select @i = @i + 1
     select @column = index_col(@tablename, @indid, @i)
    end   
 set @sql = @sql + N')'
 insert into #keymakes (sql, ord) values(@sql, 2)

    fetch next from uc_cursor into @tablename, @ucname, @indid
end
close uc_cursor
deallocate uc_cursor

-- Step 4: for every column in every table with a mismatched collation
-- issue an alter column command
insert into #tablechanges (sql)
 select 'ALTER TABLE ' +
  case
   when sysobjects.uid = @metadataSchemaId then 'MetadataSchema.' + sysobjects.name
   else sysobjects.name
  end
  +   ' ALTER COLUMN ' + syscolumns.name +
  ' ' + systypes.name +
  case
   when systypes.name = 'ntext' then ' COLLATE '
   else
    case
     when systypes.name = 'nvarchar' and syscolumns.length = -1 then '(max) COLLATE '
     else '(' + cast(syscolumns.length/2 as nvarchar(50)) + ') COLLATE '
    end
  end
   +  @target_collation +   
  case
   when syscolumns.isnullable = 0 then ' NOT NULL'
   else ' NULL'
  end 
 from syscolumns
  join sysobjects on (sysobjects.id = syscolumns.id)
  join systypes on (systypes.xtype = syscolumns.xtype and systypes.xtype = systypes.xusertype)
 where
  syscolumns.collation is not null
  and syscolumns.collation != @target_collation
  and sysobjects.type = 'U'
  and (systypes.name = 'nvarchar' or systypes.name = 'nchar' or systypes.name = 'ntext')
order by sysobjects.name, syscolumns.name

-- STEP 5: execute key drops
declare keydrops_cursor cursor for select sql from #keydrops order by id
open keydrops_cursor
fetch next from keydrops_cursor into @sql
while (@@fetch_status = 0)
begin
   print @sql
   exec (@sql)
   fetch next from keydrops_cursor into @sql
end
close keydrops_cursor
deallocate keydrops_cursor

-- STEP 6: execute table changes
declare tables_cursor cursor for select sql from #tablechanges order by id
open tables_cursor
fetch next from tables_cursor into @sql
while (@@fetch_status = 0)
begin
   print @sql
   begin try
  exec (@sql)
 end try
 begin catch
  print 'error executing: ' + @sql
  print ERROR_MESSAGE()
 end catch
   fetch next from tables_cursor into @sql
end
close tables_cursor
deallocate tables_cursor

-- STEP 7: execute key makes
declare keymakes_cursor cursor for select sql from #keymakes order by ord desc, id asc
open keymakes_cursor
fetch next from keymakes_cursor into @sql
while (@@fetch_status = 0)
begin
   print @sql
   exec (@sql)
   fetch next from keymakes_cursor into @sql
end
close keymakes_cursor
deallocate keymakes_cursor

drop table #keydrops
drop table #keymakes
drop table #tablechanges

 

Jul 31, 2009 at 2:10 AM

To  boudewijnsnoeren

Please eplain:

 

1.  Why do you drop and recreate the fn_GetFormatStrings function ?

2.  The main script is essentially a copy of p_SetDbCollation stored procedure from the target MSCRM 4 database itself  and this appears to be used in the installation process when you choose a different Collation to the default (which is Latin1_General_CI_AI )  so again why run an external script when the the procedure could be called ?

3.  Both versions omit several important aspects:

A. INCLUDE columns in indexes.

B. varchar, char, text and nvarchar(max)  columns that exist in the original installation.

Note that your script is specifying reverting back to Latin1_General_CI_AI  and you will probably not even we aware of the includes - as these would have all been wiped out in the first instance.