How to automatically clear the MSSQL Server Table Collation Problem

Source: Internet
Author: User
Tags mssql server

In a team project, there is no agreement on Collation, programming in the ms SQL Server will encounter such a problem:
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL _Latin1_General_CP1_CI_AS" in the equal to operation.

Because of the encoding problem, strings of different encodings cannot be directly compared. There are two solutions to this problem. 1 is to specify a collation for comparison in the query, the other is to modify the collation type of the column to avoid this error.

In the second method, one column is very tired, and a script is written. In addition to the varchar, char, and nvarchar used as constraints such as primary key and foreign key, you can change them to a collation...

Complete SQL code:

Copy codeThe Code is as follows: 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 (
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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.