Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Where can I configure (if it's possible) how Sql Server 2005 "interprets" character encoding of string data sent trough TDS protocol (in this case from web application)?

Maybe it would be better if I describe whole problematic situtation:

  • when inserting regional characters such as: ą, Ą, ć, Ł, ź (and so on - polish regional characters), trough TDS protocol - "INSERT INTO" query from web application, they appear in mssql table as: a (instead of ą), A (instead of Ą), c, L, z etc.
  • target column type is nvarchar(MAX)
  • I looked at data sent "trough wire" (with WireShark) and all data is sent using UTF-16 encoding, which looks ok: for example Ą is sent as U+0104.
  • when inserting same string into local database instance (Microsoft SQL Server Express Edition) it works fine, but on remote host (in customer location - it is Microsoft SQL Server Standard Edition 64-bit) this "de-regionalization" occurs. That why I suppose it is all about SQL Server configuration, because the only difference from web application side is choosing other sql server (so TDS drivers, configuration, conbection settings and so on are the same).

I hope someone could help me with this one.

share|improve this question
    
My first though was collation of the database, but that is used for storing non unicode data. Data type on the remote db is definitely 'N' type? –  Sam Sep 16 '10 at 16:21
    
Yes, it's: nvarchar(MAX) exactly. –  Davvid Sep 16 '10 at 17:05
    
Maybe try this on stackoverflow - it's application-centric enough to go there I would think. –  Sam Sep 17 '10 at 0:03

1 Answer 1

up vote 0 down vote accepted

I found the soruce of this problem. It's N prefix of Unicode string literals:

"When SQL Server converts a Unicode string without the N prefix from Unicode to the SQL Server database's code page, any characters in the Unicode string that do not exist in the SQL Server code page will be lost."

http://support.microsoft.com/kb/239530

I suppose the local instance of sqlserver in some way treats all incoming string literals as Unicode strings and that remote server doesn't, thus requiring N prefix.

Maybe it was a rocky mistake (not knowing about N prefix necessity), but I work with mysql on daily basis not mssql.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.