Re: Collation advice going from 2000 to 2008

On Jun 8, 3:00 pm, "Sylvain Lafontaine"
<sylvainlafontaine2...@xxxxxxxx> wrote:
"JimLad" <jamesdbi...@xxxxxxxxxxx> wrote in message

news:7c459f9d-e70a-4283-9eff-7aeb2ffd59b1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Hi Geoff,

Have you ever tried migrating a db with one collation onto a server
with a different collation. Then try using any code with a temp table
and see how you get on! :-)

This assumes you haven't been specifying collation on all joins etc...

James

Hi James, if you have trouble with a temp table and a collation when making
your joins, it's usually because you didn't use the collate database_default
option for the field(s) when creating the temporary table or had an improper
use of the USE datababase command before creating this temporary table; seehttp://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1368.entry.

Pay close attention to the different result for the column
[defaultCollation] in both tables when either USE TEMPDB or USE
testCollation is used before the creation of each temporary table.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Thanks Sylvain.

collate database_default is a good tip.

Given that it would be nice to use Windows collations, but is not
required, it seems like the simplest thing is to keep everything in
SQL_Latin_1_General_CP1_CI_AS. Changing every create temp table
statement would be time consuming and changing the existing database
collation sounds like a nightmare!

Many thanks,

James
.

Relevant Pages

  • Re: Collation advice going from 2000 to 2008
    ... it's more faster to change the actual create temp table statements ... than to change the existing collation of a database or to adapt the JOIN ... Sylvain Lafontaine, ing. ...
    (microsoft.public.sqlserver.server)
  • Re: Is it possible to change tempdb? a collation problem
    ... We have a similar issue on some systems where the collation at installation ... has been incorrectly set relative to user databases, causing join errors ... with temp tables. ...
    (microsoft.public.sqlserver.server)
  • Re: Collation Conflict on Cross Database Join
    ... No need to create temp tables for this. ... What you need to do is to specify for one side of the JOIN ... > table a inner join table b ... the collation sequences on table a & b are different. ...
    (microsoft.public.sqlserver.programming)
  • Re: Collation advice going from 2000 to 2008
    ... Have you ever tried migrating a db with one collation onto a server ... if you have trouble with a temp table and a collation when making ... Sylvain Lafontaine, ing. ...
    (microsoft.public.sqlserver.server)
Loading