Problem in loading Roman8 characters (ä, ü )into SQL server.

[Ram]

We are trying to load text tab delimited files into SQL server using
informatica 5.1 version. The text files are FTPed to a local server
and then files are moved to applicable folder before loading.

The problem is when we open the files after FTP the characters are
already converted to something else. EX: ü got converted as Ï.

Now when we load them into SQL server we want Ï to transfer as ü
again.

In informatica we use relational database connection and specify SQL
server as server type, there is no place where you can specify the
character conversion parameter.

Can anybody help solving this problem ?

Thanks in advance.
Ram.

[Simon Hayes]

"Ram" <ra*****@yahoo.com> wrote in message
news:6c**************************@posting.google.c om...

We are trying to load text tab delimited files into SQL server using
informatica 5.1 version. The text files are FTPed to a local server
and then files are moved to applicable folder before loading.

The problem is when we open the files after FTP the characters are
already converted to something else. EX: ü got converted as Ï.

Now when we load them into SQL server we want Ï to transfer as ü
again.

In informatica we use relational database connection and specify SQL
server as server type, there is no place where you can specify the
character conversion parameter.

Can anybody help solving this problem ?

Thanks in advance.
Ram.

I'm not sure I understand completely - are you saying that after you FTP the
file to the server's filesystem, but before loading it into MSSQL, the
characters have changed? If so, then you would have to look into how it's
FTPd, are there different Windows code pages on the servers, is the source
system a different OS etc. If the characters change when loaded into MSSQL,
then you would start by looking at the data types of the destination
columns, and the collation of the database.

Simon

[Ram]

> I'm not sure I understand completely - are you saying that after you FTP the

file to the server's filesystem, but before loading it into MSSQL, the
characters have changed? If so, then you would have to look into how it's
FTPd, are there different Windows code pages on the servers, is the source
system a different OS etc. If the characters change when loaded into MSSQL,
then you would start by looking at the data types of the destination
columns, and the collation of the database.

Simon

Hi Simon,

Thanks for your response, the text files are extracted from a server
with Roman 8 code page and they tried to FTP the files in both binary
and ASCII modes.
After FTP the results were same in both cases meaning the characters
looks exactly same when we open these text files with notepad.

So the extract team said we have to do the conversion before loading
into SQL server.

I don't know if this makes any sense but only option for me is to
convert them before loading.

Thanks,
Ram.

[Simon Hayes]

"Ram" <ra*****@yahoo.com> wrote in message
news:6c**************************@posting.google.c om...

I'm not sure I understand completely - are you saying that after you FTP the file to the server's filesystem, but before loading it into MSSQL, the
characters have changed? If so, then you would have to look into how it's FTPd, are there different Windows code pages on the servers, is the source system a different OS etc. If the characters change when loaded into MSSQL, then you would start by looking at the data types of the destination
columns, and the collation of the database.

Simon

Hi Simon,

Thanks for your response, the text files are extracted from a server
with Roman 8 code page and they tried to FTP the files in both binary
and ASCII modes.
After FTP the results were same in both cases meaning the characters
looks exactly same when we open these text files with notepad.

So the extract team said we have to do the conversion before loading
into SQL server.

I don't know if this makes any sense but only option for me is to
convert them before loading.

Thanks,
Ram.

So when the files arrive on the MSSQL server's filesystem, the data is
correct in notepad, but incorrect after loading it into the database? If so,
then perhaps you could post the destination table DDL (CREATE TABLE
statement), as well as the database collation (and the version of MSSQL
also). If you normally always load the data with Informatica, then it might
be useful to load it with DTS and/or BCP as well, to try and narrow down the
problem.

Simon

[Erland Sommarskog]

Ram (ra*****@yahoo.com) writes:

We are trying to load text tab delimited files into SQL server using
informatica 5.1 version. The text files are FTPed to a local server
and then files are moved to applicable folder before loading.

The problem is when we open the files after FTP the characters are
already converted to something else. EX: ü got converted as Ï.

Now when we load them into SQL server we want Ï to transfer as ü
again.

In informatica we use relational database connection and specify SQL
server as server type, there is no place where you can specify the
character conversion parameter.

You don't say how you load these files. Do you use BCP? DTS? BULK INSERT?

If you a load a file with BCP, there are some possibilities for conversion,
however, I am uncertain that they are workable here. You talk about Roman8,
and the Roman8 I know is a 8-bit character set that was (is?) in use on
HP-UX. I doubt that Microsoft has support for this, at least within the
realm of SQL Server. I know that MS has a product with the name "services
for Unix" in the name, but I know little of what it does.

Your best bet may be to write a simple conversion program that converts
Roman8 to the ANSI code page.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp