ebram ishaak
asked on
data stored like ????? in sql server nvarchar column
CREATE SCHEMA Product;
CREATE TABLE [Product].[ProductCategory ]
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT C_ProductCategoryName CHECK
((LEN([Name])BETWEEN 1 AND 20) AND ([Name]+'.'<>'.') AND ([Name] NOT LIKE' %') AND ([Name]NOT LIKE'% '))
);
CREATE PROC [Product].[SP_AddProductCa tegory]
(@Name NVARCHAR(20))
AS
BEGIN
INSERT INTO [Product].[ProductCategory ] VALUES (@Name)
END
EXEC [Product].[SP_AddProductCa tegory] 'T-shirt' -----result--> T-shirt
EXEC [Product].[SP_AddProductCa tegory] 'تيشيرت' -----result--> ??????
i want to add N prefix in stored procedure to be able to stored arabic values successfully when exec the stored procedure
CREATE TABLE [Product].[ProductCategory
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT C_ProductCategoryName CHECK
((LEN([Name])BETWEEN 1 AND 20) AND ([Name]+'.'<>'.') AND ([Name] NOT LIKE' %') AND ([Name]NOT LIKE'% '))
);
CREATE PROC [Product].[SP_AddProductCa
(@Name NVARCHAR(20))
AS
BEGIN
INSERT INTO [Product].[ProductCategory
END
EXEC [Product].[SP_AddProductCa
EXEC [Product].[SP_AddProductCa
i want to add N prefix in stored procedure to be able to stored arabic values successfully when exec the stored procedure
ASKER
MR Vitor
i want to solve it inside the procedure not while execution
something like @Name=N+@Name
so when execute it's not nessesary to put N prefix
i want to solve it inside the procedure not while execution
something like @Name=N+@Name
so when execute it's not nessesary to put N prefix
Does the column has an Arabic collation?
ASKER
database collation is (sql_latin_general_cp1_ci_a s)
i didnot bound any coulmn with any collation so column collation should be (sql_latin_general_cp1_ci_ as) and it's datatype is Nvarchar
i didnot bound any coulmn with any collation so column collation should be (sql_latin_general_cp1_ci_
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
MR Vitor
so what is acollation that supports arabic and english to bound the column with and is there a collation can bound to column to support any language?
thank you
so what is acollation that supports arabic and english to bound the column with and is there a collation can bound to column to support any language?
thank you
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
MR\Vitor
in my case user can store arabic or english values in the same column actually in the same field
i want in my stored procedures take parameters whatever arabic or english and execute successfully without n prefix in my
execute statements my database collation is (sql_latin_general_cp1_ci_ as) i created this columns in Nvarchar
my question is (is (sql_latin_general_cp1_ci_ as) can support arabic and english?)
-if yes, why data stored like ????? when store arabic
-if no,when i tried to change database colltion to Arabic_CI_AS give me the following error
what can i do in steps please?
thanks
in my case user can store arabic or english values in the same column actually in the same field
i want in my stored procedures take parameters whatever arabic or english and execute successfully without n prefix in my
execute statements my database collation is (sql_latin_general_cp1_ci_
my question is (is (sql_latin_general_cp1_ci_
-if yes, why data stored like ????? when store arabic
-if no,when i tried to change database colltion to Arabic_CI_AS give me the following error
what can i do in steps please?
thanks
in my case user can store arabic or english values in the same column actually in the same fieldPlease let me know how are you doing this, because I never saw a case where that's possible.
my question is (is (sql_latin_general_cp1_ci_No it can't. Can support all occidental languages as English, German, Spanish, French, Italian, Portuguese, Swedish, etc... but not oriental languages as Arabic, Chinese, Japanese, Korean, ...as) can support arabic and english?)
-if yes, why data stored like ????? when store arabicThat's a proof that it can't store Arabic characters.
-if no,when i tried to change database colltion to Arabic_CI_AS give me the following errorThat's because Arabic collation isn't compatible with Latin_General collation so SQL Server can't perform the necessary conversion.
With all that said I still keep my suggestion:
"What you can do is to create another column only for Arabic and keep the current one for Occidental languages. "
ASKER
What is the collation for the EXPERT3 database? And for t1 table and Name column? I don't think they are a sql_latin_general collation, right?
ASKER
MR\Vitor
EXPERT3 is in Arabic_CI_AS and i didn't bound any table or column with a collation so table took the database collation.
the problem is my original database collation is Sql latin general and all it's tables and columns and when i tried to change database collation to arabic error occur and when i tried to change column collation arabic doesn't exists in drop down list to choose from table design.
you think the only solution is to create a new database with arabic collation and execute original database script to it or there is a solution ?
thanks
EXPERT3 is in Arabic_CI_AS and i didn't bound any table or column with a collation so table took the database collation.
the problem is my original database collation is Sql latin general and all it's tables and columns and when i tried to change database collation to arabic error occur and when i tried to change column collation arabic doesn't exists in drop down list to choose from table design.
you think the only solution is to create a new database with arabic collation and execute original database script to it or there is a solution ?
thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
no i execute only the alter statement and it gives me the error but now i dropped the table and recreate it as
CREATE TABLE [Product].[ProductCategory ]
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(20) COLLATE Arabic_CI_AS NOT NULL UNIQUE,
CONSTRAINT C_ProductCategoryName CHECK
((LEN([Name])BETWEEN 1 AND 20) AND ([Name]+'.'<>'.') AND ([Name] NOT LIKE' %') AND ([Name]NOT LIKE'% '))
);
and it execute successfully
but the error still exists !!!!!!!!
there is no way to change the collation of database i think this may help ,
i want to thank you mr vitor for your great help and efforts
CREATE TABLE [Product].[ProductCategory
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(20) COLLATE Arabic_CI_AS NOT NULL UNIQUE,
CONSTRAINT C_ProductCategoryName CHECK
((LEN([Name])BETWEEN 1 AND 20) AND ([Name]+'.'<>'.') AND ([Name] NOT LIKE' %') AND ([Name]NOT LIKE'% '))
);
and it execute successfully
but the error still exists !!!!!!!!
there is no way to change the collation of database i think this may help ,
i want to thank you mr vitor for your great help and efforts
You're not sending the parameter with N for nvarchar:
EXEC [Product].[SP_AddProductCategory] N'تيشيرت'
ebram ishaak, is this issue solved or do you still have questions about it?
Recommendation to close this question by accepting the above comments as solution.
Open in new window