CODEWITHSUNDEEP
sql-servert-sqlcollation
Bogdan Verbenets
Bogdan Verbenets

Reputation: 26936

set collation automatically

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?

Upvotes: 1

Views: 1172

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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.

Upvotes: 2

gbn
gbn

Reputation: 432180

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

Upvotes: 3

Dalex
Dalex

Reputation: 3625

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

Upvotes: 0

Related Questions