Cannot resolve the collation - tempdb..#Carriers

i have this code

insert into #Carriers (CarrierTrackingNumber)
select distinct top 1000 CarrierTrackingNumber
from Sales.SalesOrderDetail d
      join #Carriers c on d.CarrierTrackingNumber = c.CarrierTrackingNumber
where d.ProductID = 776

this is error:
Msg 468, Level 16, State 9, Line 524
Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Msg 209, Level 16, State 1, Line 524
Ambiguous column name 'CarrierTrackingNumber'.

SELECT CONVERT (varchar, DATABASEPROPERTYEX('AdventureWorks','collation'));  
SQL_Latin1_General_CP1_CI_AS

SELECT CONVERT (varchar, DATABASEPROPERTYEX('tempdb,'collation'));  
Modern_Spanish_CI_AS
enrique_aeoAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
The collation error means that you're database, table or column has a different collation from the SQL Server instance collation. This means that you need to explicitly say which collation that you want to use:

join #Carriers c on d.CarrierTrackingNumber = c.CarrierTrackingNumber COLLATE Modern_Spanish_CI_AS
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
for ambigous error
 

 change "select distinct top 1000 CarrierTrackingNumber " to

select distinct top 1000 d.CarrierTrackingNumber


for the collation error, you can redefine the temp table with proper collation , in this case SQL_Latin1_General_CP1_CI_AS
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Ambiguous column name 'CarrierTrackingNumber'.
You need to add the table alias (c or d) in the SELECT statement. For example:

select distinct top 1000 d.CarrierTrackingNumber
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.