error in collation

• Johann Montfort

  Hall of Fame

  Points: 3123

  More actions

  August 22, 2008 at 12:27 pm

  #211492

  Dear All

  I am trying to transfer some data from an old database to a new database, however I am getting the following error:-

  Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

  My code is like this:-

  INSERT INTO #temp_OldMatches

  (

  matchID,matchDate,matchType,cupName,TeamB,clubName,Venue,HTScore,FTScore,FormTeamA,FormTeamB,Scorers,Ref,RedCards,Commentary

  )

  SELECT TOP 10 matchID,matchDate,matchType,(SELECT cupName FROM hamrunMatchesSQL.dbo.Cups WHERE cupID = hamrunMatchesSQL.dbo.Matches.matchType),

  TeamB,(SELECT clubName FROM hamrunMatchesSQL.dbo.Clubs WHERE clubID = hamrunMatchesSQL.dbo.Matches.TeamB),

  Venue,HTScore,FTScore,FormTeamA,FormTeamB,

  Scorers,Ref,RedCards, Commentary

  FROM hamrunMatchesSQL.dbo.Matches

  Can you please tell me what I have to do to get it to work

  Thanks

  Johann

• Johann Montfort

  Hall of Fame

  Points: 3123

  More actions

  August 22, 2008 at 12:56 pm

  #862080

  solved guys

  added collate SQL_Latin1_General_CP1_CI_AS in the where clause

  Thanks

• Daniel Smith-480684

  Say Hey Kid

  Points: 694

  More actions

  September 17, 2008 at 8:28 am

  #873042

  I had a similar problem today. Although this might be obvious to most people you can add COLLATE to other clauses not just the WHERE clause.

  With my problem the collation error was occuring with the two version columns:

  One came from a temporary table declared in my query, the other from a table in the DB I was testing with.

  SELECT t.*

  FROM #tempTable1 t

  INNER JOIN myView AS v ON v.intColumn = t.intColumn

  AND v.Version = t.penultimateVersion COLLATE SQL_Latin1_General_CP1_CI_AS

  I hope this is clear.