Problem: Attribute queries using Unicode strings do not work against SQL Server

Description

Attribute queries that include a Unicode string do not produce the correct result against an ArcSDE geodatabase on SQL Server.

In the following example using a query in the ArcMap Select by Attribute dialog box, the attribute column being queried contains country names in several different languages and alphabets. No results are returned by ArcMap unless the SQL Server instance being queried is using a Thai locale or collation.

Cause

Attribute queries in ArcMap are passed as-is to SQL Server. SQL Server automatically converts any character strings to the default single-byte code page that it is configured to use.

If the character string being queried contains non-local characters, e.g. from an alphabet that is different from the locale set for the dbms, then the query will return invalid results.

Solution or Workaround

To pass a Unicode string to SQL Server, use the N prefix. Place the letter N immediately before the character string. This tells SQL Server to treat the string as Unicode and not single-byte characters.

Note:If data is stored as Unicode, but does not contain any non-local characters, then it is not necessary to use the N prefix.