Solved

search in Persian fails in query analizer

Posted on 2006-11-25
18
773 Views
Last Modified: 2012-06-22
I have a windows XP server, Running SQL 2000.
i use to search in Query Analizer in Engish and Persian.
now the search in English works but the search in Persian fails.
0
Comment
Question by:Bashirmz
  • 5
  • 5
  • 4
  • +1
18 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 18011097
When you say 'fails', do you get an error, or simple no results?
What has changed on the server? Re-install of SQL? Or is it a new server with a back-up of a previous database?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 18011112
try something like this in persian

select columnname from yourtablename where columnname =n'persiandata'
0
 

Author Comment

by:Bashirmz
ID: 18011146
when i run the following:
select columnname from yourtablename where columnname =n'persiandata'
i do not get an error...but no data is returned...but if run the same but instead of persian data, english data i get bunch of records...these records are in english and persian.

i have not changed anything on the system
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 28

Expert Comment

by:imran_fast
ID: 18011148
what is the data type for your column?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 18011150
and post the exact query what you are passing if possible.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18011158
What is the collation type of the column
0
 

Author Comment

by:Bashirmz
ID: 18011159
data type nvarchar

select name from Registrant where name is ='احمد'
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 18011166
try

select name from Registrant where name is like N'%احمد%'
0
 

Author Comment

by:Bashirmz
ID: 18011171
same result....o data
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 18011183
Hi Bashir,

Try one thing run select columnname query without any condition grab the data from the cell from the grid (persian data) and paste it in the where caluse and check it.
Also tell me what is the collation of the field .
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 18011184
<same result....o data
do you have there data?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 18011198
can you please run the script below (change DBname and table name) and then post result?
------------------------------------------------------------------------------------------------------


use yourDB

declare @tblName sysname
set @tblName='yourTable'

select   COLUMN_NAME,DATA_TYPE,CASE IS_NULLABLE
             WHEN 'YES' THEN 'NULL'
             WHEN 'No' THEN 'NOT NULL'
 END as 'NULLABLE', CHARACTER_MAXIMUM_LENGTH AS LENGTH, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE object_id(TABLE_NAME) = object_id(@tblName)
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 18011220
also try

SELECT MyColumn FROM yourTable WHERE yourColumn = '&#1705;&#1740;&#1608;&#1575;&#1606;' COLLATE Arabic_CI_AS
--Unfortunately there is no Farsi collation for SQL Server
more:

Non-English languages and SQL Server
http://nayyeri.net/archive/2005/12/20/Non_2D00_English-languages-and-SQL-Server.aspx
0
 

Author Comment

by:Bashirmz
ID: 18013623
Hi Eugen,
here is what i get after running your script:

Column_Name      Data_type      Nullable      Length      Collation_ name      

REGISTRANT_ID      nvarchar      NOT NULL      57      SQL_Latin1_General_CP1_CI_AS
REGISTRANT_LC      float      NOT NULL      NULL      NULL
REG_DATA_A3      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
REG_DATA_A4      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
REG_DATA_A5      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
BADGE_LABEL      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
SALARY_GROUP      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
PRINT_DATE      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
SALARY_LOCATION      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
SALARY_DATE      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
FIELD1      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
FIELD2      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
FIELD3      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
FIELD4      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
FIELD5      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
NAME_E      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
FAMILY_NAME_E      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
FATHER_NAME_E      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
NOTE_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
BIRTH_PLACE_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
BIRTH_DATE_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
SERVICE_ID_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
NATIONAL_ID_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
SEX_E      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
PLACE_ISSUED_E      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
DATE_ISSUED_E      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
EXPIRATION_DATE_E      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
HEIGHT_E      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
WEIGHT_E      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
BLOOD_TYPE_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
HAIR_COLOR_E      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
EYE_COLOR_E      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
CURRENT_SALARY_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
AUTHORIZED_BY_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
CURRENT_RANK_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
LANGUAGE_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
ETHNICITY_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
CURRENT_ADDRESS_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
ORIGINAL_ADDRESS_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
EDUCATION_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
EDUCATION_ADDRESS_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
NATIONAL_ID_ISSUE_ADDRESS_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
NATIONAL_ID_ISSUE_DATE_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
DISTINCTIVE_MARKS_E      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
NAME      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
FAMILY_NAME      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
FATHER_NAME      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
NOTE      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
SERVICE_ID      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
NATIONAL_ID      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
BIRTH_PLACE      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
BIRTH_DATE      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
SEX      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
PLACE_ISSUED      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
DATE_ISSUED      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
EXPIRATION_DATE      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
HEIGHT      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
WEIGHT      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
BLOOD_TYPE      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
HAIR_COLOR      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
EYE_COLOR      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
CURRENT_SALARY      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
AUTHORIZED_BY      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
REG_DATA_E1      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
REG_DATA_E2      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
REG_DATA_E3      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
REG_DATA_E4      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
REG_DATA_E5      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
FINGER1_ID      float      NULL      NULL      NULL
FINGER2_ID      float      NULL      NULL      NULL
FINGER1      image      NULL      2147483647      NULL
FINGER2      image      NULL      2147483647      NULL
PICTURE      image      NULL      2147483647      NULL
LANGUAGE_ID      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
CARD_SERIAL_NUM      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
CURRENT_RANK      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
SIGNATURE      image      NULL      2147483647      NULL
LANGUAGE      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
ETHNICITY      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
CURRENT_ADDRESS      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
ORIGINAL_ADDRESS      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
EDUCATION      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
EDUCATION_ADDRESS      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
NATIONAL_ID_ISSUE_ADDRESS      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
NATIONAL_ID_ISSUE_DATE      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
DISTINCTIVE_MARKS      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
REGISTRANT_STATUS      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
CARD_STATUS      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
CARD_TYPE      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
ENROLL_STATION_ID      nvarchar      NULL      255      SQL_Latin1_General_CP1_CI_AS
REG_DATA_A1      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
REG_DATA_A2      nvarchar      NOT NULL      255      SQL_Latin1_General_CP1_CI_AS
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 18014368
Bashirmz ,
as you can see - there is just "SQL_Latin1_General_CP1_CI_AS" collation
 try to use the link examples and update existing (or add new column) with Arabic collation->  Arabic_CI_AS

try for example:
ALTER TABLE yourTable     ALTER COLUMN NAME_E  Arabic_CI_AS;
ALTER TABLE  yourTable    ADD     COLUMN  NAME_E_A     nvarchar(255) Arabic_CI_AS

more:
Books Online  
ALTER TABLE (Transact-SQL)  

http://msdn2.microsoft.com/en-us/library/ms190273.aspx



0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 18014371
...try to use the link examples...

Changing Rogue Database Collations
http://www.sqlservercentral.com/columnists/mpearson/changingroguedatabasecollations.asp
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question