how to change collations to SQL_Latin1_General_CP1_CI_AS?

Hi,
I installed SQL Server 2000 SP3a on win2003 in English with the
collation Latin1_General_CI_AS.
Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
I am not really interested to reinstall SQL-server.
I tried the rebuildm.exe-tool but it couldn´t start because some
process was still using SQL-Server (I stopped everything I knew).
Is there a special query or a software-tool which can handle this
transformation?
thanks in advance,
RagnarHi,
> I installed SQL Server 2000 SP3a on win2003 in English with the
> collation Latin1_General_CI_AS.
> Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
ONLY RESTORE?
SQL Server 2000 supports multiple collations.
SEE Books Online
[SQL Server Architecture]
+ [Database Architecture]
+ [Logical Database Components]
+ [Collations]
+ [Mixed Collation Environments]
PARA. Collations in BACKUP and RESTORE
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Ragnar Heil" <r@.gnar.de> wrote in message
news:8a70509.0309262355.5d06abc1@.posting.google.com...
> Hi,
> I installed SQL Server 2000 SP3a on win2003 in English with the
> collation Latin1_General_CI_AS.
> Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
> I am not really interested to reinstall SQL-server.
> I tried the rebuildm.exe-tool but it couldn´t start because some
> process was still using SQL-Server (I stopped everything I knew).
> Is there a special query or a software-tool which can handle this
> transformation?
> thanks in advance,
> Ragnar|||I thought that the collation of
SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
are the same.
Am I right ?|||Hi,
> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
I feel so too.
But,
Latin1_General_CI_AS (Windows collation)
SQL_Latin1_General_CP1_CI_AS (SQL collation)
And Mixed Collation Environments CAUSE Collation conflict ERROR (449)
--
SELECT CategoryName FROM Northwind.dbo.Categories
WHERE
CASE WHEN 1=1
THEN CategoryName COLLATE Latin1_General_CI_AS
ELSE CategoryName COLLATE SQL_Latin1_General_CP1_CI_AS
END
LIKE 'C%' COLLATE Latin1_General_CI_AS
--
CAUSE Collation conflict ERROR.
--
SELECT CategoryName FROM Northwind.dbo.Categories
WHERE
CASE WHEN 1=1
THEN CategoryName COLLATE Latin1_General_CI_AS
ELSE CategoryName COLLATE Latin1_General_CI_AS
END
LIKE 'C%' COLLATE Latin1_General_CI_AS
--
RETURN some records.
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:132601c3864c$393336c0$a101280a@.phx.gbl...
> I thought that the collation of
> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
> Am I right ?
>|||> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
They are not quite the same. Latin1_General_CI_AS uses Unicode sorting
rules for both Unicode and non-Unicode data. To see one difference (not
the only one), try the following:
IF 'a-c' COLLATE Latin1_General_CI_AS > 'ab' COLLATE
Latin1_General_CI_AS
PRINT 'Latin1_General_CI_AS: a-c > ab'
ELSE
PRINT 'Latin1_General_CI_AS: a-c < ab'
IF 'a-c' COLLATE SQL_Latin1_General_CP1_CI_AS > 'ab' COLLATE
SQL_Latin1_General_CP1_CI_AS
PRINT 'SQL_Latin1_General_CP1_CI_AS: a-c > ab'
ELSE
PRINT 'SQL_Latin1_General_CP1_CI_AS: a-c < ab'
> I tried the rebuildm.exe-tool but it couldn´t start because some
> process was still using SQL-Server (I stopped everything I knew).
To use rebuildm.exe, make sure that the SQL Server service is fully stopped
(e.g. go to Computer Managment --> Services and stop anything named
"MSSQLServer" or "MSSQL$...").
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: "Shinichi Yoneda" <mxl04371@.nifty.ne.jp>
References: <8a70509.0309262355.5d06abc1@.posting.google.com>
<OOX7gGVhDHA.1200@.TK2MSFTNGP12.phx.gbl>
<132601c3864c$393336c0$a101280a@.phx.gbl>
Subject: Re: how to change collations to SQL_Latin1_General_CP1_CI_AS?
Date: Mon, 29 Sep 2003 21:45:35 +0900
Lines: 48
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <ePiiaeohDHA.4024@.TK2MSFTNGP11.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: ntehme007118.ehme.nt.adsl.ppp.infoweb.ne.jp
218.229.98.118
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308787
X-Tomcat-NG: microsoft.public.sqlserver.server
Hi,
> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
I feel so too.
But,
Latin1_General_CI_AS (Windows collation)
SQL_Latin1_General_CP1_CI_AS (SQL collation)
And Mixed Collation Environments CAUSE Collation conflict ERROR (449)
--
SELECT CategoryName FROM Northwind.dbo.Categories
WHERE
CASE WHEN 1=1
THEN CategoryName COLLATE Latin1_General_CI_AS
ELSE CategoryName COLLATE SQL_Latin1_General_CP1_CI_AS
END
LIKE 'C%' COLLATE Latin1_General_CI_AS
--
CAUSE Collation conflict ERROR.
--
SELECT CategoryName FROM Northwind.dbo.Categories
WHERE
CASE WHEN 1=1
THEN CategoryName COLLATE Latin1_General_CI_AS
ELSE CategoryName COLLATE Latin1_General_CI_AS
END
LIKE 'C%' COLLATE Latin1_General_CI_AS
--
RETURN some records.
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:132601c3864c$393336c0$a101280a@.phx.gbl...
> I thought that the collation of
> SQL_Latin1_General_CP1_CI_AS and Latin1_General_CP1_CI_AS
> are the same.
> Am I right ?
>
From: "Shinichi Yoneda" <mxl04371@.nifty.ne.jp>
Subject: Re: how to change collations to SQL_Latin1_General_CP1_CI_AS?
Date: Sun, 28 Sep 2003 08:46:34 +0900
Newsgroups: microsoft.public.sqlserver.server
Hi,
> I installed SQL Server 2000 SP3a on win2003 in English with the
> collation Latin1_General_CI_AS.
> Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
ONLY RESTORE?
SQL Server 2000 supports multiple collations.
SEE Books Online
[SQL Server Architecture]
+ [Database Architecture]
+ [Logical Database Components]
+ [Collations]
+ [Mixed Collation Environments]
PARA. Collations in BACKUP and RESTORE
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Ragnar Heil" <r@.gnar.de> wrote in message
news:8a70509.0309262355.5d06abc1@.posting.google.com...
> Hi,
> I installed SQL Server 2000 SP3a on win2003 in English with the
> collation Latin1_General_CI_AS.
> Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
> I am not really interested to reinstall SQL-server.
> I tried the rebuildm.exe-tool but it couldn´t start because some
> process was still using SQL-Server (I stopped everything I knew).
> Is there a special query or a software-tool which can handle this
> transformation?
> thanks in advance,
> Ragnar|||"Shinichi Yoneda" <mxl04371@.nifty.ne.jp> wrote
> > I installed SQL Server 2000 SP3a on win2003 in English with the
> > collation Latin1_General_CI_AS.
> > Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
> ONLY RESTORE?
> SQL Server 2000 supports multiple collations.
> SEE Books Online
> [SQL Server Architecture]
> + [Database Architecture]
> + [Logical Database Components]
> + [Collations]
> + [Mixed Collation Environments]
> PARA. Collations in BACKUP and RESTORE
Hello,
I read this chapter and it seems that I just have to create a new DB,
choose the right collation and restore the DB. The message is that
multiple collations are supported and a feature of SQL 2000.
But the fact is that I can't choose "SQL_Latin1_General_CP1_CI_AS"
while creating a new DB.
So what I did is I reinstalled SQL Server, changed regional-settings
to English(US), English (UK) for the local user.
Then I got the right collation.
cheers
Ragnar|||Why you can't choose "SQL_Latin1_General_CP1_CI_AS" while
creating a new DB.
I got a SQL server which has server collation
Latin1_General_CI_AS. It is the same as yours. I can
create a brand new db with collation
SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS.|||On Tue, 30 Sep 2003 21:51:20 -0700, "Johnny" <jtao@.ssc.nsw.gov.au>
wrote:
>Why you can't choose "SQL_Latin1_General_CP1_CI_AS" while
>creating a new DB.
>I got a SQL server which has server collation
>Latin1_General_CI_AS. It is the same as yours. I can
>create a brand new db with collation
>SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS.
Yes, but then you will have problems with temp tables, as they will be
created with the same collation as the master database by default.
You can get around this issue, by specifying
"collation database_default" for each character-type column when you
create your temp tables. Of course, that means you need to create them
up-front, and can't use Select ... Into #xxx From ... anymore.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander|||Hi,
(a)TRY
SELECT * FROM ::fn_helpcollations()
YOU CAN SEE list of all the collations supported by YOUR SQL Server 2000.
EX.
Latin1_General_CI_AS,Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive
SQL_Latin1_General_CP1_CI_AS,Latin1-General, case-insensitive,
accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
> But the fact is that I can't choose "SQL_Latin1_General_CP1_CI_AS"
> while creating a new DB.
(b)creating a new DB. BUT WITHOUT COLLATION
RESTORE DATABASE WITH REPLACE
the existing database is completely replaced with the contents of the
database on the backup file.
(AFTER RESTORE, COLLATION SQL_Latin1_General_CP1_CI_AS)
(c)FOR Rebuildm.exe YOU MUST COPY FILES FROM CD TO HDD. REMOVE Read-only
attribute.
http://support.microsoft.com/default.aspx?scid=kb;en-us;273572
BUG: Rebuildm.exe Utility Stops Responding When Source Directory is on a CD
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Ragnar Heil" <r@.gnar.de> wrote in message
news:8a70509.0309300437.7b617e5b@.posting.google.com...
> "Shinichi Yoneda" <mxl04371@.nifty.ne.jp> wrote
> > > I installed SQL Server 2000 SP3a on win2003 in English with the
> > > collation Latin1_General_CI_AS.
> > > Now I want to restore a DB with SQL_Latin1_General_CP1_CI_AS.
> > ONLY RESTORE?
> > SQL Server 2000 supports multiple collations.
> > SEE Books Online
> > [SQL Server Architecture]
> > + [Database Architecture]
> > + [Logical Database Components]
> > + [Collations]
> > + [Mixed Collation Environments]
> > PARA. Collations in BACKUP and RESTORE
> Hello,
> I read this chapter and it seems that I just have to create a new DB,
> choose the right collation and restore the DB. The message is that
> multiple collations are supported and a feature of SQL 2000.
> But the fact is that I can't choose "SQL_Latin1_General_CP1_CI_AS"
> while creating a new DB.
> So what I did is I reinstalled SQL Server, changed regional-settings
> to English(US), English (UK) for the local user.
> Then I got the right collation.
>
> cheers
> Ragnar

How to change collation settings of a database

Hi,
what would be the best way to change the collation settings of a database? I
need to change the collation from SQL_Latin1_General_CP1_CI_AS to
Latin1_General_CI_AS.
Thank you a lot in advance!
Danielhttp://msdn.microsoft.com/library/d...br />
819v.asp
ML|||Hi,
have a look at the alter database TSQL in BOL. Thanks
--
--
Thanks,
Ibrahim
If you find this post useful, please click 'yes'
"Daniel Walzenbach" wrote:

> Hi,
> what would be the best way to change the collation settings of a database?
I
> need to change the collation from SQL_Latin1_General_CP1_CI_AS to
> Latin1_General_CI_AS.
> Thank you a lot in advance!
> Daniel|||Thank you ML!
Regards
Daniel
"ML" wrote:

> http://msdn.microsoft.com/library/d... />
a_819v.asp
>
> ML|||Just be make sure you perused the previously posted Books Online topic,
please note the following excerpt:
<Excerpt>
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:
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
news:E4DEF1D9-83C8-4762-A99E-6B67F94C6261@.microsoft.com...
> Hi,
> what would be the best way to change the collation settings of a database?
> I
> need to change the collation from SQL_Latin1_General_CP1_CI_AS to
> Latin1_General_CI_AS.
> Thank you a lot in advance!
> Daniel|||Hi Dan,
the excerpt you posted is exactly the problem I have. Do you see any
solution to solve this?
Thank you!
Daniel
"Dan Guzman" wrote:

> Just be make sure you perused the previously posted Books Online topic,
> please note the following excerpt:
> <Excerpt>
> Altering the default collation of a database does not change the collation
s
> of the columns in any existing user-defined tables. These can be changed
> with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement change
s:
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
> news:E4DEF1D9-83C8-4762-A99E-6B67F94C6261@.microsoft.com...
>
>|||Unfortunately, there is no easy way to change the collation of all existing
columns to the new collation. You'll need to recreate the tables. See
http://support.microsoft.com/defaul...b;en-us;325335.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
news:F704636E-CA46-42DD-AD1B-07D15243351F@.microsoft.com...
> Hi Dan,
> the excerpt you posted is exactly the problem I have. Do you see any
> solution to solve this?
> Thank you!
> Daniel
> "Dan Guzman" wrote:
>|||Dan,
I feared so ;-)
Thanks for you reply! Regards
Daniel
"Dan Guzman" wrote:

> Unfortunately, there is no easy way to change the collation of all existin
g
> columns to the new collation. You'll need to recreate the tables. See
> http://support.microsoft.com/defaul...b;en-us;325335.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
> news:F704636E-CA46-42DD-AD1B-07D15243351F@.microsoft.com...
>
>