Re: Collation advice going from 2000 to 2008

Well, 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
statements if the collation of the temp table and of the database is
different when moving to a new server.

Ideally, you would have use the collate database_default from the beginning
and this way, you would have been immune to the possibility of having a temp
database collation different from the collation of your database.

--
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)


"JimLad" <jamesdbirch@xxxxxxxxxxx> wrote in message
news:b49ae99f-be85-4d39-aa46-f0fee54e193b@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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: Change field collation
    ... constraints and statistics on the columns before you can alter ... There are quite a lot of caveats and you use this script at your own risk. ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.programming)
  • Re: Changing collation -- URGENT
    ... You can use the following script to change the collations of your columns, ... duplicate primary key constraints create statements, ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.server)
  • Re: Database collation error after moving to a remote sql server
    ... I think I'm going to try plan B: Install a second instance of SQL 2000 onto ... the remote server and set its default collation to the same as my SMS ... database, then move my db to the new instance. ... The char, varchar, text, nchar, nvarchar, or ntext system data types, ...
    (microsoft.public.sms.setup)
  • Re: COLLATION PROBLEM CAN ANY ONE HELP
    ... Sounds like you may have code page X data in a code page Y database. ... database with a collation that matches the data's actual code page: ... COLLATION PROBLEM CAN ANY ONE HELP ... | Content-Type: text/plain; ...
    (microsoft.public.sqlserver.datamining)
  • Re: Can we change the Colloation setting for an existing database?
    ... SQL Server MVP ... > I have a database have a different collation setting from the temp db. ...
    (microsoft.public.sqlserver.server)