Solved

search in Persian fails in query analizer

Posted on 2006-11-25
18
765 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
Comment Utility
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
Comment Utility
try something like this in persian

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

Author Comment

by:Bashirmz
Comment Utility
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
 
LVL 28

Expert Comment

by:imran_fast
Comment Utility
what is the data type for your column?
0
 
LVL 28

Expert Comment

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

Expert Comment

by:Nightman
Comment Utility
What is the collation type of the column
0
 

Author Comment

by:Bashirmz
Comment Utility
data type nvarchar

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

Expert Comment

by:imran_fast
Comment Utility
try

select name from Registrant where name is like N'%احمد%'
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Bashirmz
Comment Utility
same result....o data
0
 
LVL 28

Expert Comment

by:imran_fast
Comment Utility
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
Comment Utility
<same result....o data
do you have there data?
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
...try to use the link examples...

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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now