CODEWITHSUNDEEP
sqlsql-server-2012
Dom Sinclair
Dom Sinclair

Reputation: 2528

How can I resolve a collation conflict

I am in the process of trying to move data from one database to another. In the old database there is a column of datatype DateTime, which needs to be mapped to a new column in the new database which is of type Date.

To try and accomplish this I have written the following sql query

    UPDATE NewFishTrackerProfessional.dbo.LandingHeaders
  SET NewFishTrackerProfessional.dbo.LandingHeaders.LandingDate1 = (SELECT CONVERT(DATE,FishTracker.dbo.Landings.LandingDate) FROM FishTracker.dbo.Landings WHERE LandingHeaders.TicketNumber = FishTracker.dbo.Landings.TicketNo)

when executed however this produces the following error.

1 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. SQL1.sql 2 1

Can anyone suggest a way that it might be possible to resolve this?

Thanks

The original database was written back in sqlserver 2005 , the new database was created in sqlserver 2012. I no longer have easy access to a 2005 instance of sqlserver.

Upvotes: 0

Views: 95

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

The problem is probably not on the date (and definitely if we assume that you really are using native types). That leaves the where clause. So, use the COLLATE operator:

UPDATE NewFishTrackerProfessional.dbo.LandingHeaders lh
    SET lh.LandingDate1 = (SELECT CONVERT(DATE, l.LandingDate)
                           FROM FishTracker.dbo.Landings l
                           WHERE lh.TicketNumber COLLATE Latin1_General_CI_AS  = ls.TicketNo COLLATE Latin1_General_CI_AS
                          );

I don't know which value is in which collation, so I just put the operator on both sides. You should probably put the operator only the side with lh, the query can use an index on Landings.TicketNo.

EDIT:

That suggests that you have multiple values that match the WHERE. The simplest method is to choose one, either using TOP or an aggregation function:

UPDATE NewFishTrackerProfessional.dbo.LandingHeaders lh
    SET lh.LandingDate1 = (SELECT TOP 1 CONVERT(DATE, l.LandingDate)
                           FROM FishTracker.dbo.Landings l
                           WHERE lh.TicketNumber COLLATE Latin1_General_CI_AS  = ls.TicketNo COLLATE Latin1_General_CI_AS
                          );

or

UPDATE NewFishTrackerProfessional.dbo.LandingHeaders lh
    SET lh.LandingDate1 = (SELECT MAX(CONVERT(DATE, l.LandingDate))
                           FROM FishTracker.dbo.Landings l
                           WHERE lh.TicketNumber COLLATE Latin1_General_CI_AS  = ls.TicketNo COLLATE Latin1_General_CI_AS
                          );

Upvotes: 1

Related Questions