SQL Server set collation automatically

aydmsdu9 于 2023-10-15 发布在其他

关注(0)|答案(3)|浏览(123)

My application's database mydb has different collation than tempdb. I get a lot of exceptions comparing string values between tempdb temporary tables and mydb persistent tables. It was decided to create mydb with same collation as tempdb. The collation must be set automatically using script. I've tried this:

DECLARE @SQLCollation sql_variant
SELECT @SQLCollation = DATABASEPROPERTYEX('tempdb', 'Collation')
ALTER DATABASE mydb COLLATE @SQLCollation -- doesn't work
ALTER DATABASE mydb COLLATE Latin1_General_CI_AS -- works, but doesn't suit me because I have to type in collation myself in this SQL

So how do I set the same collation for mydb as for tempdb?

sql-server

来源：https://stackoverflow.com/questions/7872857/set-collation-automatically

3条答案

按热度按时间

fzwojiic1#

You can't just change DB collation with ALTER DATABASE . This only changes system databases (object names etc). (Dalex's answer)

You have to follow the steps detailed in the answers to Changing SQL Server Database sorting .

Another option to use the COLLATE Database_Default to coerce collation without knowing what it is. See SQL Server error "Implicit conversion of because the collation of the value is unresolved due to a collation conflict." and SQL Server - is there a way to mass resolve collation conflicts

赞(0）回复(0）举报 2023-10-15

erhoui1w2#

Changing collation of the db will not change the collation of already existing tables in the db.

Another option would be to specify the collation to use when you create your temp table.

create table #TempTable
(
  Name varchar(10) collate database_default
)

Your comparisons will then work just fine asuming that all your tables character fields have the same collation as the database.

赞(0）回复(0）举报 2023-10-15

bn31dyow3#

DECLARE @SQLCollation NVARCHAR(1000)
SELECT @SQLCollation = 'ALTER DATABASE MyDb COLLATE '+CAST(DATABASEPROPERTYEX('Tempdb', 'Collation') as NVARCHAR(1000))
exec (@sqlcollation)

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server set collation automatically

3条答案

相关问题

热门标签

最新问答