collation conflict

• Scott Pedersen

  Newbie

  Points: 8

  More actions

  December 13, 2004 at 1:28 pm

  #60030

  Hi,

  I am new to SQL Server but not to the SQL language. I currently have a select clause which produces the following error when run -

  'Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict'

  The SQL that is causing the problem is -

  set concat_null_yields_null OFF;

  select

     ltrim( str(id) )+',"'+

     replace( name,'"','''' ) + '",' +

     ltrim( str( numberoflines ) ) +',"'+

     replace( descriptiontext,'"','''' ) + '",' +

     ltrim( str( stclassid ) )

  from

     dbo.STType

  The select runs ok if I take out the line 'replace( name,'"','''' ) + '",' +

  ' or the line 'replace( descriptiontext,'"','''' ) + '",' +

  '. When they are both included in the select clause the above error occurs.

  The type for both these columns is varchar but they are of different sizes -

  [Name] [varchar] (50) COLLATE Latin1_General_CS_AS NOT NULL

  [DescriptionText] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL

  If any one has run into this type of error before and solved it could you please let me know, it would be much appreciated.

  Just another note, the SQL Server database that we are selecting the data out of can't be modified in anyway, as it doesn't belong to the company I work for.

  Thanks for your time.

  Regards,

  Scott.

   

• Wesley Brown

  SSChampion

  Points: 12045

  More actions

  December 13, 2004 at 2:09 pm

  #533645

  DescriptionText COLLATE Latin1_General_CS_AS

  in BOL under topic Collation Precedence

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  December 13, 2004 at 2:12 pm

  #533647

  The problem is that the system cannot reconcile CI (Case INsensitive) with CS (Case Sensitive).  Try the below

  SELECT CAST([Name] AS VARCHAR(50))      COLLATE Latin1_General_CS_AS,

    CAST(DescriptionTest AS VARCHAR(255)) COLLATE Latin1_General_CS_AS

  This will convert BOTH to be Case Sensitive and hopefully will work for you

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• Scott Pedersen

  Newbie

  Points: 8

  More actions

  December 13, 2004 at 2:22 pm

  #533649

  Thanks AJ, it worked like a treat