Re: Changing collation -- URGENT

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Uri Dimant (urid_at_iscar.co.il)
Date: 03/03/04 

Date: Wed, 3 Mar 2004 13:34:35 +0200

Gyk
BOL says
You can also use the COLLATE clause on an ALTER DATABASE to change the
default collation of the database:

ALTER DATABASE MyDatabase COLLATE French_CI_AS
Altering the default collation of a database does not change the collations
of the columns in any existing user-defined tables. These can be changed
with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:

CREATE TABLE MyTable
  (PrimaryKey int PRIMARY KEY,
   CharCol varchar(10) COLLATE French_CI_AS NOT NULL
  )
GO
ALTER TABLE MyTable ALTER COLUMN CharCol
            varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
GO

"GYK" <anonymous@discussions.microsoft.com> wrote in message
news:DCC92136-EE54-41F7-83ED-A07AC7BADDCC@microsoft.com...
> Hi,
>
> I need an urgent help.
>
> I have a database backup file with 30,000 tables, which has a particular
collation SQL_Latin1_General_CP1_CI_AS.
> Now I need to restore this database on a different server (srv2), but with
different collation Latin1_General_BIN.
> So I created a database on srv2 with Latin1_General_BIN collation. But
When I tried to restore it from the backup file, am getting all the tables
with SQL_Latin1_General_CP1_CI_AS collation. I tried to change the database
collation on srv2 to Latin1_General_BIN , but still the collation is the old
one.
>
> Can anybody provide me the solution to this?
>
> Thanks
> GYK

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 issue
    ... ALTER DATABASE will only change collation for future tables. ... > exception of case sensitive searches, so the following command is being ...
    (microsoft.public.sqlserver.programming)
  • 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)