javascript jquery and ms sql unicode incompatibility

I have developed a site (classic asp), it saves data in ms sql. At first it had no javascript to speak ok. I have updated the app to use jquery extensively thus javascript.

I am forced to use utf-8. Originally I used iso-8859-1, all was ok. the ms sql database stored and retrieved the data perfectly

Now The site uses unicode and and the data is stored with unicode and special characters are saved as 2  characters. This is unacceptable, it makes the site unusable

I have tried to use different collations in the tables to no avail.

When googling all I get is a confirmation f this issue between ms sql and  unicode.
 
Any ideas?
robrodpProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
What is the datatype of the target column?  You will need to store unicode values in NVARCHAR columns instead of VARCHAR.  I'm just going to the obvious first in case you overlooked it.  It is a simple matter to convert a column from VARCHAR to NVARCHAR.

Example:

ALTER TABLE <myTable>
ALTER COLUMN <myColumn> NVARCHAR(255)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robrodpProgrammerAuthor Commented:
It is nvarchar

Thx
Olaf DoschkeSoftware DeveloperCommented:
UTF-8 is not unicode, you need conversion of UCS-2 to UTF-8.

There are several levels where this can go wrong:

1. The webserver announces an encoding in its HTTP header
2. The html pages might containn contradicting encoding information
3. the ODBC driver used to query MSSQL might translate from UCS-2 to ANSI already.

You need to get all of this working together, there is no single solution for this, you need to address all of the vectors, eg find out what the Server has set as default encoding, see how you set your html and determine how ODBC translates MSSQL data.

Absolute no gos are storing UTF-8 in SQL Server tables, T-SQL string functions work with UCS-2 Unicode encoding and many functions would not work on UTF-8 strings interpreted as UCS-2, eg comparsions with = operator or LIKE, substrings, etc.

Without knowing any of your code of JQuery and DB access its impossible to recommend anything.

Bye, Olaf.
robrodpProgrammerAuthor Commented:
Working on this

Thx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.