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

Count related items (javascript)

Jim Wang has recently
posted an article to view the totals of activities and hisoty items.


This article was really very usefull to implement for other related entities.
So I hacked into the code and made sure these counts are done asynchronously.

When items are count, the users sees the "loading" icon while waiting for the result.


CountRelatedItems = function(entityName, filterAttribute, filterValue, navObject) {
if (navObject != null) {

var originalImage = null;

//set loading image
if (navObject.childNodes != null && navObject.childNodes[0] != null) {
originalImage = navObject.childNodes[0].src;
navObject.childNodes[0].src = '/_imgs/btn_lookup_resolving.gif';
}

var xml = "" +
"" +
"" + GenerateAuthenticationHeader() +
" " +
" " +
" ";
xml += " " + entityName + "";
xml += " " +
" " +
" statuscode" +
" " +
" " +
" false" +
" " +
" And" +
" " +
" " +
" statuscode" +
" Equal" +
" " +
" 1" +
" " +
" " +
" ";
xml += " " + filterAttribute + "";
xml += " Equal" +
" " +
" " + filterValue + "" +
" " +
" " +
" " +
" " +
" " +
" " +
" " +
"" +
"";

var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", true);
xmlHttpRequest.onreadystatechange = function() {
if (xmlHttpRequest.readyState == 4) {
var buNodes = xmlHttpRequest.responseXML.selectNodes("//BusinessEntity/q1:statuscode"); // CRM 4.0
if (navObject != null) {
navObject.getElementsByTagName('NOBR')[0].innerText =
navObject.getElementsByTagName('NOBR')[0].innerText + " (" + buNodes.length + ")";
//set original image
if (navObject.childNodes != null && navObject.childNodes[0] != null)
navObject.childNodes[0].src = originalImage;
}

}
}
xmlHttpRequest.setRequestHeader("SOAPAction", " http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);
}
}

To call this function, for example to count the number of related contacts to an account, the following syntax can be used:

CountRelatedItems("contact", "parentaccountid", crmForm.ObjectId,
document.getElementById('navContacts'));

Workflow activities on codeplex

I have created a codeplex project with several common workflow activities.
You can download the code here