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