Re: How do I change the default collation string of an installation

David Greenberg (davidgr@xxxxxxxxxx) writes:
When we installed SqlServer2000 we left the default collation name
(Sql_Latin1_General_CPI_CI_AS).
The user defined databases we created afterwards were defined with a
different collation name in order to be able to accept the character set
we use, Hebrew.
We are looking into switching DTSs that we use to copy data from our
main system , that uses an Ingres database, into OSQL scripts. Although
the DTSs successfully copy the Hebrew letters when I copy data with OSQL
it comes over as jibberish.
After looking into the matter I came to the conclusion that while DTS
refers to specific databases and uses the destination database's
collation name , OSQL refers to the remote server and destination server
and therefore uses the collation name of the server and not of the
database. In order for it to successfully copy the Hebrew I need to
change the default collation name of the installation.
Is "rebuild master" the way to do such a thing ? (this is a production
server so we are wary of doing a "rebuild master")
Has anyone else run into similar problems when transfering data between
servers using OSQL ?

Changing server collation indeed requires use of rebuildm, however I
think you don't have to do it. In fact, I think it would not even
help you.

The problem with OSQL is different. OSQL looks at what is your OEM code
page, and then converts data as it was in that code page. So even with
a server in Hebrew, you would still get gibberish. (I'm assuming that
the ANSI and OEM pages for Hebrew are different, as they are for
Latin-1.)

True, you could save the data in the OEM code page, and now it would
work - but proabbly not if the server collation is
Sql_Latin1_General_CPI_CI_AS. But since ANSI/OEM conversion is not
always roundtrip, I don't recommend that.

Instead I recommend that you use Unicode files when you work with OSQL.
That should relieve you of all conversion problems.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • How do I change the default collation string of an installation
    ... The user defined databases we created afterwards were defined with a different collation name in order to be able to accept the character set we use, ... We are looking into switching DTSs that we use to copy data from our main system, that uses an Ingres database, into OSQL scripts. ... After looking into the matter I came to the conclusion that while DTS refers to specific databases and uses the destination database's collation name, OSQL refers to the remote server and destination server and therefore uses the collation name of the server and not of the database. ...
    (comp.databases.ms-sqlserver)
  • Re: Different Collation designator Settings
    ... You can have databases with different collations on SQL ... That wasn't possible on SQL Server 7. ... values in temporary tables while assuming that tempdb has the same collation ...
    (microsoft.public.sqlserver.connect)
  • Re: Different Collation designator Settings
    ... You can have databases with different collations on SQL ... That wasn't possible on SQL Server 7. ... values in temporary tables while assuming that tempdb has the same collation ...
    (microsoft.public.sqlserver.server)
  • Re: Collation problem
    ... Unfortunately changing the server collation isn't going to help you much at ... this point -- as you noticed, your databases won't be updated. ... AFAIK, no good way to change the collation of an entire database, due to the ... -- "Rob Meade" wrote in message ...
    (microsoft.public.sqlserver.programming)
  • Re: Different Collation designator Settings
    ... You can have databases with different collations on SQL ... That wasn't possible on SQL Server 7. ... values in temporary tables while assuming that tempdb has the same collation ...
    (microsoft.public.sqlserver.setup)