How Do You Collate in an Union All Statement

• adams.squared

  SSCrazy

  Points: 2675

  More actions

  April 12, 2011 at 8:40 am

  #235500

  I've used collate in the past to join two tables together from different servers with different collation using a linked server, but now I need to bring together two sets of data with different collation by using UNION ALL. How

  SELECT InvNumber

  FROM [SERVERA].[DatabaseA].dbo.[TableA]

  UNION ALL

  SELECT OrderNumber

  FROM [SERVERB].[DatabaseB].dbo.[TableB]

  Database A is SQL_Latin1_General_CP1_CI_AS

  Database B is Latin1_General_Bin

  I have to come up with a solution that does not involve altering any databases.

  Thanks

• Richard Warr

  SSCertifiable

  Points: 6967

  More actions

  April 12, 2011 at 8:50 am

  #1310436

  Can you say

  SELECT OrderNumber COLLATE SQL_Latin1_General_CP1_CI_AS

  FROM [SERVERB].[DatabaseB].dbo.[TableB][/quote]

  in the second bit?

• adams.squared

  SSCrazy

  Points: 2675

  More actions

  April 12, 2011 at 9:01 am

  #1310448

  Yes I can say that.

  Thanks. That did the job.