if object_id('mydbr_languages', 'U') is null begin
create table mydbr_languages(
lang_locale char(5) PRIMARY KEY,
language varchar(30),
date_format varchar(10) null,
time_format varchar(10) null,
thousand_separator varchar(2) null,
decimal_separator varchar(2) null
)
end
go
if (select dbo.fn_mydbr_column_exists('mydbr_languages', 'date_format'))=0 begin
alter table mydbr_languages add date_format varchar(10) null
alter table mydbr_languages add time_format varchar(10) null
alter table mydbr_languages add thousand_separator varchar(2) null
alter table mydbr_languages add decimal_separator varchar(2) null
end
go
ALTER TABLE mydbr_languages ALTER COLUMN date_format varchar(10) null
ALTER TABLE mydbr_languages ALTER COLUMN time_format varchar(10) null
ALTER TABLE mydbr_languages ALTER COLUMN thousand_separator varchar(2) null
ALTER TABLE mydbr_languages ALTER COLUMN decimal_separator varchar(2) null
ALTER TABLE mydbr_reports ALTER COLUMN parameter_help nvarchar(max) null
go
/* We'll use temp table for mass insert. Kind of insert ignore */
create table #mydbr_languages_tmp (
lang_locale char(5),
language varchar(30),
date_format varchar(10) null,
time_format varchar(10) null,
thousand_separator varchar(2) null,
decimal_separator varchar(2) null
)
go
insert into #mydbr_languages_tmp (language, lang_locale) values('Arabic', 'ar_SA')
insert into #mydbr_languages_tmp (language, lang_locale) values('Bulgarian', 'bg_BG')
insert into #mydbr_languages_tmp (language, lang_locale) values('Chinese', 'zh_CN')
insert into #mydbr_languages_tmp (language, lang_locale) values('Croatian', 'hr_HR')
insert into #mydbr_languages_tmp (language, lang_locale) values('Czech', 'cs_CZ')
insert into #mydbr_languages_tmp (language, lang_locale) values('Danish', 'da_DK')
insert into #mydbr_languages_tmp (language, lang_locale) values('Dutch', 'nl_NL')
insert into #mydbr_languages_tmp (language, lang_locale) values('English', 'en_US')
insert into #mydbr_languages_tmp (language, lang_locale) values('British English', 'en_GB')
insert into #mydbr_languages_tmp (language, lang_locale) values('Estonian', 'et_EE')
insert into #mydbr_languages_tmp (language, lang_locale) values('Finnish', 'fi_FI')
insert into #mydbr_languages_tmp (language, lang_locale) values('French', 'fr_FR')
insert into #mydbr_languages_tmp (language, lang_locale) values('German', 'de_DE')
insert into #mydbr_languages_tmp (language, lang_locale) values('Greek', 'el_GR')
insert into #mydbr_languages_tmp (language, lang_locale) values('Hungarian', 'hu_HU')
insert into #mydbr_languages_tmp (language, lang_locale) values('Icelandic', 'is_IS')
insert into #mydbr_languages_tmp (language, lang_locale) values('Italian', 'it_IT')
insert into #mydbr_languages_tmp (language, lang_locale) values('Japanese', 'ja_JP')
insert into #mydbr_languages_tmp (language, lang_locale) values('Korean', 'ko_KR')
insert into #mydbr_languages_tmp (language, lang_locale) values('Latvian', 'lv_LV')
insert into #mydbr_languages_tmp (language, lang_locale) values('Lithuanian', 'lt_LT')
insert into #mydbr_languages_tmp (language, lang_locale) values('Norwegian', 'no_NO')
insert into #mydbr_languages_tmp (language, lang_locale) values('Polish', 'pl_PL')
insert into #mydbr_languages_tmp (language, lang_locale) values('Portuguese', 'pt_PT')
insert into #mydbr_languages_tmp (language, lang_locale) values('Romanian', 'ro_RO')
insert into #mydbr_languages_tmp (language, lang_locale) values('Russian', 'ru_RU')
insert into #mydbr_languages_tmp (language, lang_locale) values('Slovak', 'sk_SK')
insert into #mydbr_languages_tmp (language, lang_locale) values('Slovenian', 'sl_SI')
insert into #mydbr_languages_tmp (language, lang_locale) values('Spanish', 'es_ES')
insert into #mydbr_languages_tmp (language, lang_locale) values('Swedish', 'sv_SE')
insert into #mydbr_languages_tmp (language, lang_locale) values('Turkish', 'tr_TR')
go
update #mydbr_languages_tmp
set date_format = 'm/d/Y', time_format = 'h:i:s a', thousand_separator = ',', decimal_separator = '.'
where lang_locale in ('en_US')
go
update #mydbr_languages_tmp
set date_format = 'd/m/Y', time_format = 'H:i:s', thousand_separator = ',', decimal_separator = '.'
where lang_locale in ('en_GB')
go
update #mydbr_languages_tmp
set date_format = 'Y-m-d', time_format = 'H:i:s', thousand_separator = ',', decimal_separator = '.'
where lang_locale in ('zh_CN')
go
update #mydbr_languages_tmp
set date_format = 'Y-m-d', time_format = 'H:i:s', thousand_separator = ' ', decimal_separator = ','
where lang_locale in ('sv_SE', 'lt_LT')
go
update #mydbr_languages_tmp
set date_format = 'Y.m.d', time_format = 'h:i:s a', thousand_separator = ',', decimal_separator = '.'
where lang_locale in ('ko_KR')
go
update #mydbr_languages_tmp
set date_format = 'Y.m.d', time_format = 'H:i:s', thousand_separator = ' ', decimal_separator = ','
where lang_locale in ('hu_HU')
go
update #mydbr_languages_tmp
set date_format = 'd.m.Y', time_format = 'H.i.s', thousand_separator = ' ', decimal_separator = ','
where lang_locale in ('fi_FI', 'el_GR')
go
update #mydbr_languages_tmp
set date_format = 'd.m.Y', time_format = 'H:i:s', thousand_separator = ' ', decimal_separator = ','
where lang_locale in ('cs_CZ', 'el_GR', 'bg_BG', 'et_EE', 'lv_LV', 'no_NO', 'pl_PL', 'ro_RO', 'ru_RU', 'sk_SK', 'sl_SI', 'hr_HR')
go
update #mydbr_languages_tmp
set date_format = 'd.m.Y', time_format = 'H:i:s', thousand_separator = '.', decimal_separator = ','
where lang_locale in ('de_DE', 'is_IS', 'tr_TR')
go
update #mydbr_languages_tmp
set date_format = 'd-m-Y', time_format = 'H:i:s', thousand_separator = '.', decimal_separator = ','
where lang_locale in ('nl_NL')
go
update #mydbr_languages_tmp
set date_format = 'd/m/Y', time_format = 'H.i.s', thousand_separator = '.', decimal_separator = ','
where lang_locale in ('it_IT', 'da_DK', 'pt_PT')
go
update #mydbr_languages_tmp
set date_format = 'd/m/Y', time_format = 'H:i:s', thousand_separator = ' ', decimal_separator = ','
where lang_locale in ('fr_FR', 'es_ES', 'ar_SA')
go
update #mydbr_languages_tmp
set date_format = 'd/m/Y', time_format = 'H:i:s', thousand_separator = ',', decimal_separator = '.'
where lang_locale in ('ja_JP')
go
delete from mydbr_languages
where lang_locale not in (
select lang_locale
from #mydbr_languages_tmp
)
go
update mydbr_languages
set
mydbr_languages.date_format = t.date_format,
mydbr_languages.time_format = t.time_format,
mydbr_languages.thousand_separator = t.thousand_separator,
mydbr_languages.decimal_separator = t.decimal_separator
from #mydbr_languages_tmp t
where mydbr_languages.lang_locale=t.lang_locale
go
insert into mydbr_languages (lang_locale, language, date_format, time_format, thousand_separator, decimal_separator)
select lang_locale, language, date_format, time_format, thousand_separator, decimal_separator
from #mydbr_languages_tmp
where lang_locale not in (
select lang_locale
from mydbr_languages
)
go
update mydbr_languages
set date_format = t.date_format, time_format = t.time_format,
thousand_separator = t.thousand_separator, decimal_separator = t.decimal_separator
from #mydbr_languages_tmp t
where mydbr_languages.lang_locale = t.lang_locale
go
drop table #mydbr_languages_tmp
go
I am getting following error
Msg 468, Level 16, State 9, Line 745
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Line 759
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Line 762
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
Msg 468, Level 16, State 9, Line 775
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Please help