Collation issue between tempdb and the database xxx

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Collation issue between tempdb and the...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Aparna-1

Posted Wednesday, November 04, 2009 6:00 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, June 14, 2012 11:57 PM
Points: 257, Visits: 595

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

Post #813941

gandhi.mk

Posted Thursday, November 05, 2009 3:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 19, 2013 4:01 AM
Points: 1, Visits: 10

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

Post #814097

Aparna-1

Posted Monday, November 09, 2009 9:25 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, June 14, 2012 11:57 PM
Points: 257, Visits: 595

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

Post #816288

« Prev Topic | Next Topic »

Permissions