Microsoft dynamics CRM: Change collation

In order to change the collation of the Microsoft Dynamics CRM 4 database, you should use this query:

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 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