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 3 9931
David Greenberg (da*****@iba.or g.il) writes: 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 ?
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, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
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: David Greenberg (da*****@iba.or g.il) writes:
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 ?
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.
David Greenberg (da*****@iba.or g.il) writes: 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 ?
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.
How can I create a Unicode file of the data I am bringing from the Ingres VMS server and read it into the SqlServer ?
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, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Captain Flack |
last post by:
I have a SQL Server database hosted with a web hosting company. The
SQL Server was clearly set up to be case sensitive, however, I want
this particular database to be case-insensitive.
I have searched high and low, the best suggestion I can find is to
reinstall SQL Server and select case-insensitive. But since this is
the web host's SQL, that isn't an option here.
With default language I can use the sp_defaultlanguage to change to...
|
by: kalyan |
last post by:
Hi,
If I have a database with collation Sequence X and I change the
collation sequence of database to Collation Sequence Y , do I have to
migrate the data of tables with collation Sequence X to collation
Sequence Y or SQl server takes care of migrating the data internally.
Thanks in advance.
-Kalyan
|
by: topolou |
last post by:
I've installed SQL Server 2000 with Arabic collation during the
installation. Yet, I'm not able to search for Farsi strings. What
should I do?
|
by: downlode |
last post by:
Hi,
I am writing to a text column in my SQL Server 2000 database. The text
comes from a web form in my java web application, where the character
encoding is ISO-8859-1. (I have no control over the charset, my app is
a plugin inside another app.)
Characters such as €(ascii 128) and '(ascii 146) are inserted into
the db as '?'.
I'm connecting using the free jtds driver, and I'm not specifying any
|
by: Steve Jorgensen |
last post by:
Hi all,
Frankly, this is such an off-beat thing, I don't know if anyone else here will
ever have the same issue. Just in case anyone does, though...
I needed to be able to search for the first string in a recordset that is the
same as a specified string value, or the one that would be right after that if
it doesn't exist. Several searches per second may occur, so I needed to
search within the recordset first, and only query the...
| |
by: Ryan |
last post by:
I've stumbled across the above error and am a little stuck.
I've just installed SQL2000 (sp3) on my PC and restored a database from
our Live server. On a simple Update statement on a #temp table, it
fails with the above message. I think I understand what it means and
found some old posts suggesting using the following :
select name, databasepropertyex(name, 'collation')
from master..sysdatabases
select serverproperty('collation')
|
by: papaja |
last post by:
I created sql database on one MSSQL2000 server and set up asp.net 2.0
security. Now I'm working on another computer. I moved all files and
backed up and restored database from old server to this new one.
When I try to add user using VS2005 Website ASP.NET Confiuguration I
got error. Still, user is added but not asigned to role. When I try to
asign it a role it doesn't work. Error that I get is related to server
collation. Old and new...
|
by: Henrootje |
last post by:
I have a lot of tables that hold a lot of numeric fields.
The names of all of these tables start with ' tblRO'
Now it turns out that all of the numeric fields with type double
precision have the property 'schaal' (<- dutch, the one above
'precision' ) set to zero. This should be '10' .
Is there a way to change this through VBA? Using a loop or so?
TIA Henro
|
by: Tony |
last post by:
Hello!
My first question:
I just can't figure out what is the usefulness of
Comparer.Default.Compare(somestring1, somestring2);
because I can just the same use somestring1.CompareTo(somestring2);
One more thing is about the following text:
"Strings are processed according to the current culture. To process strings
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |