Just a day before one of our SQL SERVER 2005 needed Case-Sensitive Binary Collation. When we install SQL SERVER 2005 it gives options to select one of the many collation. I says in words like ‘Dictionary order, case-insensitive, uppercase preference’. I was confused for little while as I am used to read collation like ‘SQL_Latin1_General_Pref_Cp1_CI_AS_KI_WI’. I did some research and find following link which explains many of the SQL SERVER 2005 collation.
Complete documentation MSDN – SQL SERVER Collation
Reference : Pinal Dave (https://blog.sqlauthority.com)
25 Comments. Leave new
Hi Pinal,
I have an interesting and strange question for SQL collations.
Issue: I work for a search engine. I have a column that sores the keywords that users enter for search criteria. Now there are some chinese, japanese or say thai users who enter keywords in their native language. I would like my “Text_keyword” to store data in any language and any fonts so that i would be able to store the information from multiple users across the globe. I am mentioning the other required specifications for you kind perusal.
column name: Text_keyword
Datatype: Nvarchar(max)
Collation name: Japanese_unicode_CI_AS
I have selected all of the “CODE PAGE CONVERSION TABLES” by going to >> control panel >> Regional and Language options >> Advanced options.
Sample script that I have stored in my database.
如果?不需要的一切權力指數調諧嚮導 , 但仍希望協助創建索引的表, ?可以使用”表現指數分析”選項設下的”?詢 “菜單上的?詢分析器SQL Server中 7 . 與 SQL Server 2000 ,選擇 “索引調整嚮導 ” ,從 “?詢 “菜單中的?詢分析器. 而是採用了輪廓跟蹤?案進行了分析,它使用了?詢 ,發現在?詢窗口. 雖然未能全面分析作為一個?從用指數調諧嚮導 , 這是一個很好的出發點時 ,進行了性能分析,具體問題 ,在?的數據庫 . [ 7.0 , 2000年] 2007年3月6日更新
If u noitce this script there are some question marks (?) in it.
There are supposed to be some other chinese characters in it which my sql server 2005 is not able to store and thus giving me those ? signs.
Any reply from your side would be highly appreciated.
Thanks & Regards,
Amit Pandey
DBA SQL Server
Espire Infolabs Pvt.Ltd
Hi Dave,
I have a problem on save Thai character in any table. I’m using SQL Server 2005 and I’ve changed the collation into THAI_CI_AS. I don’t know what setup else that I miss, every I save by using Thai character, there was always ‘????’ character appear on the field.
Could you give me some suggestion?
Thanks,
Afin
Hi pinal,
I have a table with some columns where both english and chinese data are stored. when i query the table, the chiese characters appear like barcodes. However, when i copy the particular data and paste it in the query pane or any text editor(MS word , notepad etc), i am able to see the proper chinese text. Can you please explain why this is so?
Also, when i query the same table with a ‘where’ condition involving that particular column and paste the same chinese text as the condition value, the query doesn’t show up any results. Any help in this regard would be of great help. Thanks.
P.S: I’m still a newbie to sql server 2005
Hi.
I was having a boatload of trouble with Chinese characters in SQL Server 2005.
Found some things that worked.
If the column is of ‘n’ type, line nchar or nvarchar, I had much more of a chance of it working right.
Also, since I was retrieving the data if from an IIS, the client’s IE had to have the right encoding; in my case since I was using Simplified Chinese, the Big5 encoding (which is Traditional Chenese) would display incorrect characters.
So, all I can say is: 谢谢!
–Joe
i have 3 table in sql-serverhaving coloumn quantity, i have to deduct the quantity from table1-table2 = table3
any help me out, how to write the query for this.
its Just an example…
what is your filtering condition that you can add….
update table3
set QUANTITY = (select QUANTITY from table1 where id = 3) –
(select QUANTITY from table2 where id = 3)
where id = 3
For Arifin Query anwser:
Unicode string constants that appear in code executed on the server, such as in stored procedures and triggers, must be preceded by the capital letter N. If a string literal is not in Unicode (marked with the N prefix), the string is converted to the default code page of the database, which may not recognize certain characters. With multilingual data it is best to use a Unicode data type and Unicode string literals.
The following example shows a Unicode string designated by placing an “n” prefix in front of the string:
Copy Code
SELECT n’’
Figure 11 shows how this string, which is the Hindi word for the Hindi language, would appear on a computer that had the correct Hindi font installed.
Hi Pinal,
I am trying to implement service broker between two different servers.
I am using following scripts to create Initiatore service brokers’s object:
USE master;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N”;
GO
CREATE LOGIN InitiatorUser with password = ‘Password#123′;
CREATE USER InitiatorUser ;
GO
CREATE CERTIFICATE InstInitiatorCertificate
AUTHORIZATION InitiatorUser
WITH SUBJECT = N’Initiator Certificate’,
START_DATE = N’12/31/2008′;
BACKUP CERTIFICATE InstInitiatorCertificate
TO FILE =
N’C:\InstInitiatorCertificate.cer’;
GO
IF EXISTS (SELECT * FROM sys.endpoints
WHERE name = N’InstInitiatorEndpoint’)
DROP ENDPOINT InstInitiatorEndpoint;
GO
CREATE ENDPOINT InstInitiatorEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE InstInitiatorCertificate);
GO
—I have created following certificate and user on target machine and from target machine I am creating following certificate.
CREATE LOGIN TargetUser with password = ‘Password#123′;
CREATE USER TargetUser ;
CREATE CERTIFICATE InstTargetCertificate
AUTHORIZATION TargetUser
FROM FILE =
N’\\NHQGLBDM001\Ankur\InstTargetCertificate.cer’;
GO
grant connect on endpoint::InstInitiatorEndpoint to targetuser
go
—————Service Broker Objects—————-
CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
(
[//BothDB/2InstSample/RequestMessage]
SENT BY INITIATOR,
[//BothDB/2InstSample/ReplyMessage]
SENT BY TARGET
);
GO
CREATE QUEUE InstInitiatorQueue;
CREATE SERVICE [//InstDB/2InstSample/InitiatorService]
AUTHORIZATION InitiatorUser
ON QUEUE InstInitiatorQueue;
GO
CREATE USER TargetUser WITHOUT LOGIN;
CREATE CERTIFICATE InstTargetCertificate
AUTHORIZATION TargetUser
FROM FILE = ‘\\Nhqglbdm001\Ankur\InstTargetCertificate.cer’
GO
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME = ‘//TgtDB/2InstSample/TargetService’,
ADDRESS = ‘TCP://NHQGLBDM001:4022′;
EXEC (@Cmd);
SET @Cmd = N’USE msdb’
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
‘[//InstDB/2InstSample/InitiatorService]’,
ADDRESS = ‘TCP://PNEITS1C1321D:4022′;
DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N’USE InstInitiatorDB’;
EXEC (@Cmd);
GO
CREATE REMOTE SERVICE BINDING TargetBinding
TO SERVICE
N’//TgtDB/2InstSample/TargetService’
WITH USER = TargetUser;
GO
————————–Sending message—————————–
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);
BEGIN TRANSACTION;
BEGIN DIALOG @InitDlgHandle
FROM SERVICE [//InstDB/2InstSample/InitiatorService]
TO SERVICE N’//TgtDB/2InstSample/TargetService’
ON CONTRACT [//BothDB/2InstSample/SimpleContract]
WITH
ENCRYPTION = ON;
SELECT @RequestMsg = N’Message for Target service.’;
SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
(@RequestMsg);
SELECT @RequestMsg AS SentRequestMsg;
COMMIT TRANSACTION;
GO
following scripts to create Target service brokers’s object:
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N”;
GO
CREATE LOGIN TargetUser with password = ‘Password#123′;
CREATE USER TargetUser ;
GO
CREATE CERTIFICATE InstTargetCertificate
AUTHORIZATION TargetUser
WITH SUBJECT = N’Target Certificate’,
START_DATE = N’12/31/2008′;
BACKUP CERTIFICATE InstTargetCertificate
TO FILE =
N’C:\InstTargetCertificate.cer’;
GO
GO
IF EXISTS (SELECT * FROM master.sys.endpoints
WHERE name = N’InstTargetEndpoint’)
DROP ENDPOINT InstTargetEndpoint;
GO
CREATE ENDPOINT InstTargetEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE InstTargetCertificate);
GO
–Following certificate is already created on Initiator machine and we are creating it from there.
CREATE LOGIN InitiatorUser with password = ‘Password#123′;
CREATE USER InitiatorUser ;
CREATE CERTIFICATE InstInitiatorCertificate
AUTHORIZATION InitiatorUser
From FILE =
N’\\PNEITS1C1321D\shared\InstInitiatorCertificate.cer’;
grant connect on endpoint::InstTargetEndpoint to InitiatorUser
———————–Service Broker objects—————
CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
([//BothDB/2InstSample/RequestMessage]
SENT BY INITIATOR,
[//BothDB/2InstSample/ReplyMessage]
SENT BY TARGET
);
GO
CREATE QUEUE InstTargetQueue;
CREATE SERVICE [//TgtDB/2InstSample/TargetService]
AUTHORIZATION TargetUser
ON QUEUE InstTargetQueue
([//BothDB/2InstSample/SimpleContract]);
GO
USE InstTargetDB
GO
CREATE USER InitiatorUser WITHOUT LOGIN;
CREATE CERTIFICATE InstInitiatorCertificate
AUTHORIZATION InitiatorUser
FROM FILE =
‘\\Pneits1c1321d\Shared\InstInitiatorCertificate.cer’;
GO
DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N’USE InstTargetDB’;
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
‘//InstDB/2InstSample/InitiatorService’,
ADDRESS = ‘TCP://Pneits1c1321d:4022′;
EXEC (@Cmd);
SET @Cmd = N’USE msdb’
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
‘//TgtDB/2InstSample/TargetService’,
ADDRESS = ‘TCP://NHQGLBDM001:4022′;
EXEC (@Cmd);
GO
GRANT SEND
ON SERVICE::[//TgtDB/2InstSample/TargetService]
TO InitiatorUser;
GO
CREATE REMOTE SERVICE BINDING InitiatorBinding
TO SERVICE N’//InstDB/2InstSample/InitiatorService’
WITH USER = InitiatorUser;
GO
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;
BEGIN TRANSACTION;
WAITFOR
( RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM InstTargetQueue
), TIMEOUT 5000;
SELECT @RecvReqMsg AS ReceivedRequestMsg;
IF @RecvReqMsgName = N’//BothDB/2InstSample/RequestMessage’
BEGIN
DECLARE @ReplyMsg NVARCHAR(100);
SELECT @ReplyMsg =
N’Message for Initiator service.’;
SEND ON CONVERSATION @RecvReqDlgHandle
MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
(@ReplyMsg);
END CONVERSATION @RecvReqDlgHandle with cleanup;
END
SELECT @ReplyMsg AS SentReplyMsg;
COMMIT TRANSACTION;
GO
—
I am able to see message in sys.transmission_queue view but transmission_status field value is coming blank.
And also can see a row in sys.dm_broker_connections view in CONVERSING state.
But unable to see message at target machine.
Could you please provide me a any help on it.
This very important coz I have to implement in my project.
Sarita
I have made a table of users with password field. but my problem is that I want nobaby can read that password even the users table is run.
The table is made in SQL Server 2000.
Could you please help me providing the code in VB .NET
with best regards,
Rabindranath
Rabindranath,
the answer is simple, don’t store it in plain text to begin with. SQL Server has encryption built in, but better to do one way encyption on the text before storing it – look at hashing and encryption in .Net it pretty straight forward
I am a beginner in SQL Server 2005. I want to work on dual languages in this. Please guide me how can i create a unicode databse and can save data in English and Hindi simulatneously.
Thanks and Regards
Allice
Hello all!
Great site and great people!!
Got a lot of help from here!!
Preeti
sir
i m using asp .net and sql as a back end
after connection i have a problem to show hindi fonts from dababase and insert hindi into database
i inserted water with its hindi meaning and than i got
???? marks in data base and when i execute ???? marks hi execute hota hai please help me sir
Hi Atul,
There are two things to keep in mind while storing unicode data. First the column must be of unicode data type (nchar, nvarchar, ntext). Second the value must be prefixed with N while insertion. For example
INSERT INTO table (Hindi_col) values (N’hindi data’)
Regards,
Pinal Dave
Dear Dave,
Please help me in same case as you helped atul. Same kind of problem i am also facing.
Actually i m doing a project in which there is a need to use the krutidev 066 font which uses symbol ‘ for the character (sheeti bala sha) and at the backend sqlserver distinguishes the string by separating through same symbol i.e. ( ‘ ).
I am have used all the parameters as you explained above but still same syntax problem is persist. I have changed datatype from varchar to nvarchar and have used the N prefix but no solution. Please help me….
Thanks in advance
Kk
Pinal,
I am currently working with a set of database done in unicode and with nvarchar fields. I have recently been required to bring in foreign language data (including Japanese, Chinese, etc) that are in varchar fields. Now I need to join and compare the nvarchar fields to the new varchar fields.
I know that there is a implicit conversion between nvarchar and varchar when joining them together, but in my case the varcahr data was simply not encoded properly and, hence, does not match up with the better nvarchar data.
How can I cast, convert, collate a the fields to get the nvarchar field to correctly match with the varchar field? Is this possible? Assuming I know the codepage for the data can I upgrage a varchar field into nvarchar? Or downgrade the nvarchar field into a varchar?
Thanks for the help,
Mike
Hi pinal,
Normaly data will be stored as case insensitive in sql server 2008.but for password validation it will give problem right?is there any suggestion for comparision purpose?
sir i have a problem in SQLServer2005 i want to save Hindi Text like in Database and also wants to retrieve hindi text but it will shows me like please help me
Use nvarchar datatype to store the data
hello dave,
i want to store hindi data in the database but there is no such collation for any indian languages
please give a solution for it
Search for indic collations
Hi Pinal,
I need information about inner join and outer join script . Is it same as inner join and outer join ?
thanks
how do i pass hindi parameters in sql server 2008 procedure as i wanna to insert a row with hindi text….there is no option for coalition
Declare the parameter as nvarchar, and while inserting add ‘N’ as prefix.