Post reply

Collation Sequence difference between table variables and temp tables

  • November 4, 2011 at 7:01 am

    #259543

    I have a query developed on one server, which runs fine.

    However when I try to run it on another SQL 2005 server, it barfs with

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

    No COLLATE clause is used in the query or table definitions

    In the query where it goes wrong, there is a temporary table joined to a table variable.

    I Checked: tempdb has SQL_Latin1_General_CP1_CI_AS and the db I'm using has Latin1_General_CI_AS

    It looks like the table variable uses the properties of the current db, rather than tempdb.

    Is there a way to make all temporary objects use the same collation sequence within a script - some SET option perhaps

  • November 4, 2011 at 7:33 pm

    #1403562

    If all the non-tempdb databases use the same collation but is different than TempDB, wouldn't it be easier to just change the default collation of TempDB?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 9, 2011 at 5:03 am

    #1405075

    In a perfect world, yes.

    However we have this DBA.... to cut a long story short ... we don't even bother asking any more.

  • November 9, 2011 at 7:22 am

    #1405159

    The best way to ensure the correct collation for a temp table is to always use SELECT INTO to create them.

    (This also has the advantage that if the column width changes in the original table it is reflected in the temp table.)

    eg

    SELECT *

    INTO #temp

    FROM YourTable

    The alternative is to explicitly declare the default db collation for text columns when creating temp tables.

    CREATE TABLE #temp

    (

    id int NOT NULL

    ,TestText varchar(20) COLLATE DATABASE_DEFAULT NOT NULL

    )

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply