April 10, 2003 at 7:33 am
Hello,
I have a problem with string literals in sql server 2000. First have a look at the following two queries:
INSERT INTO MyTable (MyUnicodeField) VALUES ('AnyValue');
SELECT * FROM MyTable WHERE MyUnicodeField='AnyValue';
It works fine as long as 'AnyValue' does not contain any special characters (e.g. umlauts). If it contains special characters, the second query does not return the record which was inserted by the first query, at least not always.
I suspect that the problem is that the string literal 'AnyValue' is first converted to the systems codepage and then back to unicode and that all special characters which are not present in the current system codepage are converted to other characters before the string literal is actually compared to the values stored in the database. If I change the two queries and add the character N before the string literal, everything seems to work fine:
INSERT INTO MyTable (MyUnicodeField) VALUES ( N'AnyValue' );
SELECT * FROM MyTable WHERE MyUnicodeField = N'AnyValue';
The server is sql server 2000, the client program is an asp script on IIS 5.0. The codepage of the asp script is set to 65001 (utf-8). The script connects to my sql server database via ADO and the SQLOLEDB provider. Note that the problem is exactly the same if I use Query Analyzer which ships with sql server.
I now could change all my sql queries im my existing scripts and add the N before each and every string literal and everything would work. The problem is that there are so many queries that I am not able to change all of them in a reasonable amount of time.
So my question is: is there any possibility to tell sql server not to mess with my string literals and leave them as unicode WITHOUT adding the N in front of every string literal of every query in my whole program ?
Thank you in advance for your help.
April 10, 2003 at 8:25 pm
Is the datatype for this particular column nvarchar, nchar or ntext? These support unicode and should eliminate your problem.
Darren
Darren
Darren
April 11, 2003 at 1:05 am
The datatype is nvarchar. The problem is not the datatype of the column but the literal in my sql query. If the leading N before the literal is omitted, the literal seems to be converted to the systems default code page before the query is actually executed by the database. So any special character which is not in the current default codepage is lost:
SELECT * FROM MyTable WHERE MyUnicodeField='Bär';
is actually executed as
SELECT * FROM MyTable WHERE MyUnicodeField='Bar';
I hope you are able to see the special character in the first query.
April 11, 2003 at 2:21 am
Not sure if this is still relevant with SQL 200/Windows 2000, but we had a similar problem with NT4 and SQL7 a while back, and it was down to the way the client machines were configured.
These are the instructions we gave for configuring the client machines (instructions were for non-techies):-
To enable international (accented) characters to be properly handled, the following steps must be carried out on the Client:
*From the Start Menu select 'Settings', then 'Control Panel'.
*From Control Panel, double click 'Regional Settings'
*Under the 'Regional Settings' tab, select 'English (United States)'
*Ensure the 'Set as System Default Locale' option is ticked.
*Press 'OK'. (At this point you may be asked to insert the Windows NT CD-ROM)
*You will then be asked to restart the computer. Select 'No'
*From the Start Menu select 'Run', then type 'regedit' and press
'OK'.
*Navigate through the tree structure until you locate the following entry:-
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Control
NLS
CodePage
*On the right hand panel there will be an entry for 'OEMCP' (which
will probably be set to 437). Double click the 'OEMCP' entry. On the
subsequent screen , type 1252 and press 'OK'
*Exit the Registry Editor
*Restart the machine.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy