Collation issue between tempdb and the database xxx - Better Solution?

• Aparna-1

  SSCrazy

  Points: 2339

  More actions

  November 4, 2009 at 6:00 pm

  #213389

  Hi ,

  I have run into collation issues between tempdb and the database.

  tempdb - collation is SQL_Latin1_General_CP1_CI_AS

  Database(xxx) is Latin1_General_CI_AS

  My Sp errors out reporting the collation conflict when a join is based on string fields.

  I have temperorily fixed this issue by forcing my temp table to use the database xxx collation.

  EX:

  CREATE TABLE #BusinessDate

  (

  RequestID VARCHAR(10) COLLATE Latin1_General_CI_AS,

  FileName VARCHAR(100) COLLATE Latin1_General_CI_AS,

  FileTypeID INT,

  BusinessDate VARCHAR(8) COLLATE Latin1_General_CI_AS

  )

  And Now my Sp works correctly.

  Is there a better solution where i can define in my sp to force the collation to the database default once rather than defining for every column of the temp table?

  Thanks in advance

  Aparna

• gandhi.mk

  SSC Journeyman

  Points: 85

  More actions

  November 5, 2009 at 3:38 am

  #1075226

  Hi,

  Try with collate statement in the TSQL Query.

  Example after where class " Collate DATABASE_default "

  Gandhi M.K.

  Agaram Instrument, skalar.

  gandhi.mk@agaramindia.com

• Aparna-1

  SSCrazy

  Points: 2339

  More actions

  November 9, 2009 at 9:25 pm

  #1077067

  Hi,

  I could not find a solution to define at the sp level but I have found a way to make it more generic

  using database_default

  So if there are collation issues because of differences in the tempdb and the database in which you are running the query use database_default for more generic solution

  CREATE TABLE #BusinessDate

  (

  RequestID VARCHAR(10) COLLATE database_default,

  FileName VARCHAR(100) COLLATE database_default,

  FileTypeID INT,

  BusinessDate VARCHAR(8) COLLATE database_default

  )

  Aparna