Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Collation issue between tempdb and the database xxx - Better Solution? Expand / Collapse
Author
Message
Posted Wednesday, November 4, 2009 6:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 5:50 PM
Points: 257, Visits: 601
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
Posted Thursday, November 5, 2009 3:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, November 9, 2009 9:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 5:50 PM
Points: 257, Visits: 601
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 »

Add to briefcase

Permissions Expand / Collapse