ASP with SQL database in Unicode and double-byte environment

[xfile]

Dear all:

Would like to consult you with a question that has been bothering us a lot
in the past two weeks.

We recently purchased a software developed with ASP (well, we knew it would
be better with .Net, but could not find one with similar features).

And we are using this application in Windows Server 2003 environment and MS
SQL 2000 (with Collation set to Chinese).

The problem is that we consistently got random characters from those ASP
pages and dynamic web pages using the data from SQL.

We have checked and reset MS SQL many times and are sure that it does
support Chinese characters and data there are fine.

We even put the <% @CodePage="950" %> on every page but still with the same
problem.

Interestingly enough, if we use MS Access, everything works fine (with the
same CodePage). All Chinese characters can display normally.

Does anyone know what might be the cause? Our application vendor seems
unable to solve the problem.

We don't even know if this is caused by the application or database.

Many thanks.

 

[David Gugick]

Does anyone know what might be the cause? Our application vendor
seems unable to solve the problem.

We don't even know if this is caused by the application or database.

Many thanks.

Does your database use UNICODE data types: nchar, nvarchar, ntext?

 

[xfile]

Hi,

We set the entire collation to Windows Collation in Chinese_Taiwan

And I am not sure if that applies to your question?

Kindly tell me who to check if those are true?

Many thanks.

 

[David Gugick]

Hi,

We set the entire collation to Windows Collation in Chinese_Taiwan

And I am not sure if that applies to your question?

Kindly tell me who to check if those are true?

Many thanks.

You need to check the data types used in the tables to see if the vendor
is using unicode data types. You can't store unicode data in non-unicode
data types.

 

[xfile]

Hi,

Got it. We need to change to Unicode.

Many thanks.

 

[David Gugick]

Hi,

Got it. We need to change to Unicode.

Many thanks.

It might not be that easy if this is a vendor application since there
might be application support for non-unicode data types.

The best thing to do is check with the vendor and get them to tell you
whether the application supports unicode data.

 

[xfile]

Hi,

Thanks. We've done it for this part.

The vendor claimed it support Unicode with language scripts but it is not
exactly working as they claimed.

We used their scripts to create database and tables and fields and so on,
but it did not even set the data type correctly.

It turned out we had to re-set the data type, and re-adjust length due to
SQL's limitation on 8060.

We are trying to solve another Unicode problem for their e-mail messages,
which I will post later.

Many thanks for your kind help.

 

[David Gugick]

Hi,

Thanks. We've done it for this part.

The vendor claimed it support Unicode with language scripts but it is
not exactly working as they claimed.

We used their scripts to create database and tables and fields and so
on, but it did not even set the data type correctly.

It turned out we had to re-set the data type, and re-adjust length
due to SQL's limitation on 8060.

We are trying to solve another Unicode problem for their e-mail
messages, which I will post later.

Many thanks for your kind help.

If you are hitting the 8060 byte row limit, you might have table design
problems. That's an incredibly large row size. Consider using NTEXT
columns for long text to get the data out of the row.

 

[xfile]

Hi,

You are absolutely correct but we were reluctant to say it about the
inappropriate design of the database.

However, we have not time to fix this problem at this stage but would do it
at a later time.

Thanks again for the tips which will be very useful.