Cannot resolve the collation conflict between
"Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal
to operation.
declare @CollationName varchar(500);
set @CollationName = 'SQL_Latin1_General_CP1_CI_AS'
create table #tmp (sqlStr varchar(max));
insert into #tmp
select 'alter table [' + o.name + '] alter column [' + c.name + ']' +
(case c.system_type_id when 167 then ' varchar(' when 175 then ' char(' else ' nvarchar(' end)
+ convert(varchar,c.max_length) + ') collate ' + @CollationName
from sys.columns c,
sys.objects o
where c.object_id=o.object_id and o.type='U' and c.system_type_id in (167,175,231) and collation_name<>@CollationName
and c.name not in (
15: select cc.COLUMN_NAME
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE cc
where
cc.TABLE_NAME = pk.TABLE_NAME
and cc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
while (exists (select * from #tmp))
begin
declare @sqlStr varchar(max);
select @sqlStr=(select top 1 sqlstr from #tmp);
exec(@sqlStr)
delete from #tmp where sqlStr=@sqlStr
end
drop table #tmp;
分享到:
相关推荐
在一个团队项目中,没有约定好Collation, 在 MS SQL Server中编程就会遇到这样的问题:Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the ...
经常发现同事爲了更改DB的Collation而删掉DB,重新创建过。其实,只要一条SQL就可以解,唔使哽麻烦!!
1. The Evolution of Microsoft SQL Server: 1989 to 2000 [加入我的離線書架] . SQL Server: The Early Years . Ron's Story . Kalen's Story . Microsoft SQL Server Ships . Development Roles Evolve . OS...
[collate collation_name] [for load |for attach] ([NAME=LOGICA_FILE_NAME,] FILENAME=’文件名’ [,size =size ] [,maxsize={max_size|unlimited} [,filegrowth=growth_increment [,n] name :数据库名称,数据库...
校勘(collation)是指对代码页、字母大小写、音调、语言和字母表的整理,很多校勘都是在数据进入数据库之前进行的,本文介绍了一些相关的校勘方法,以及数据库对多种字符集的处理方法。
1.character_set_server和collation_server 这两个变量是服务器的字符集。在系统启动的时候可以通过–character-set-server和–collation-server来设置它们。如果没有的话系统会把这两各变量设置成默认值latin1和...
- MS SQL Server/Azure: driver version was updated, support of AD MSI authentication was added - Elasticsearch: metadata reading was fixed (schema/table names escaping) - Pervasive SQL driver ...
打印sql语句还是中文 到数据库发现乱码,本以为是hibernate搞的,结果证明不是hibernate 自己也是网上解决办法 自己的问题解决了 希望能帮助其他人 运行命令:SHOW VARIABLES LIKE 'character_set_%'; 结果如下 '...
sql
collation-server = utf8_general_ci character-set-server = utf8 5)初始化 keystone 数据库 su -s /bin/sh -c "keystone-manage db_sync" keystone ( 6)初始化 Fernet keystone-manage fernet_setup --...
Support for SQL Server Compact Edition 4.0 is added SQLite data provider User-defined function for SQLite provider is supported Default UniNoCase collation for SQLite provider is added (the ...
在以前用oracle的时候,很少关于它的collation方法,但是在mysql中,这点不加注意的话,却有可能会出现问题。
MySQL会出现中文乱码的原因不外乎下列几点: 1.server本身设定问题,例如还停留在latin1 2.table的语系设定问题(包含character与collation) 3.客户端程式(例如php)的连线语系设定问题
Table: PE_Address Schema dbo Descriptions 地址表 Fields PK Name Data type Not null Unique Row GUID Collation Default Description AddressID int 地址ID ...
40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -- -- Create schema neohotel -- -- CREATE DATABASE IF NOT EXISTS neohotel; USE neohotel; -- -- Definition of table `...
MySQL免安装版配置 ...default-collation=gbk_chinese_ci 2). 如果要在中文环境的服务器端使用mysql命令行,改变my.ini文件中mysql的默认字符集。 [mysql] # set character set default-character-set=gbk
[MySQL][關聯式資料庫][DB]_語法教學#02__建立與刪除資料庫,_簡單說明Character與Collation的選
1.创建数据库 CREATE DATABASE database_name [ON {[PRIMARY] (NAME=logical_file_name, FILENAME=’os_filename_name’, [,SIZE=size] [,MAXSIZE={max_size︳UNLIMITED}] ...[COLLATE collation_name]