How do I change the default collation string of an installation

[David Greenberg]

Hi
When we installed SqlServer2000 we left the default collation name
(Sql_Latin1_Gen eral_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 ?

Thanks
David Greenberg

[Erland Sommarskog]

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

David Greenberg (davidgr@iba.or g.il) writes:[color=blue]
> When we installed SqlServer2000 we left the default collation name
> (Sql_Latin1_Gen eral_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 ?[/color]

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_Gene ral_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@sommarsk og.se

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

[David Greenberg]

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

Thank you for answering.
Could you please explain to me how to use Unicode files when you work
with OSQL. I'm not too familiar with it. Do you have any links to
information about it that you could send me ?
How can I create a Unicode file of the data I am bringing from the
Ingres VMS server and read it into the SqlServer ?
Thank you
David Greenberg

Erland Sommarskog wrote:[color=blue]
> David Greenberg (davidgr@iba.or g.il) writes:
>[color=green]
>>When we installed SqlServer2000 we left the default collation name
>>(Sql_Latin1_G eneral_CPI_CI_A S).
>>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 ?[/color]
>
>
> 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_Gene ral_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.
>[/color]

[Erland Sommarskog]

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

David Greenberg (davidgr@iba.or g.il) writes:[color=blue]
> Thank you for answering.
> Could you please explain to me how to use Unicode files when you work
> with OSQL. I'm not too familiar with it. Do you have any links to
> information about it that you could send me ?[/color]

There is no way to tell OSQL that the input is Unicode, but it detects
this automatically. For this to work, it is important that the file
starts with a Byte-Order Mark (BOM), which is FEFF or FFFE depending
if you are on Small Endian or Big Endian machine. For Windows it should
be small FFFE for Small Endian.
[color=blue]
> How can I create a Unicode file of the data I am bringing from the
> Ingres VMS server and read it into the SqlServer ?[/color]

Since I don't know Ingres, and Unicode wasn't invented in the days I worked
with VMS, I can't tell you can create a Unicode file in that end. I would
expect it to be possible, though.

However, there are several encodings of Unicode. SQL Server uses UCS-2,
where each character are stored as 16 bits. On other platforms, UTF-8
is popular. In this format ASCII characters take up one byte, and other
characters takes up 2-4 bytes. Some Windows tool can read UTF-8, but
not SQL. Even if Ingres/VMS creates UCS-2 files, you should still check
that they include a BOM. Use a hex editor to view the files.

On Windows, you can always open the file in Notepad, and then Save as
Unicode. Notepad will add a BOM for you. Several other Windows editors
are capable to do this, for instance Query Analyzer.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

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