CODEWITHSUNDEEP
sqlsql-serverdatabase
RalphBiggerton
RalphBiggerton

Reputation: 199

Solving a Collation Problem between Two Tables

I have the following SQL script, trying to perform a union on two tables located on the same database. One table appears to have a different collation type 

select * from ns.TurnsCOS 
union all 
select * from ns.TurnsValue

After running, I get the following error

enter image description here

I've read the other posts related to fixing collation issues, but adding 'Collate Latin1_General_Bin' after the from clause doesn't seem to work

How best can I resolve this collation issue between the two tables?

Upvotes: 1

Views: 859

Answers (1)

Dale K
Dale K

Reputation: 27225

You have to change the collation of each column individually e.g.

select OtherCols, Col1, Col6, Col7
from ns.TurnsCOS 
union all 
select OtherCols, Col1 collate SQL_Latin1_General_CP1_CI_AS, Col6 collate SQL_Latin1_General_CP1_CI_AS, Col7 collate SQL_Latin1_General_CP1_CI_AS
from ns.TurnsValue

You can permanently change the column collation as follows - but you should be very cautious doing so as it may then conflict with other existing queries:

ALTER TABLE [TableName] ALTER COLUMN [Col_Name] 
            [Existing Data-Type] COLLATE [New_Collation] [NULL | NOT NULL];  
GO

Ref: https://learn.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation?view=sql-server-2017

Upvotes: 4

Related Questions