Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.
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
(@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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
database collation is (sql_latin_general_cp1_ci_a
i didnot bound any coulmn with any collation so column collation should be (sql_latin_general_cp1_ci_
i didnot bound any coulmn with any collation so column collation should be (sql_latin_general_cp1_ci_
Mind that if you don't change the column collation you'll never be able to store Arabic characters into it even if the column data type is nvarchar.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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
No, there's no collation to support any language. What you can do is to create another column only for Arabic and keep the current one for Occidental languages.
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_
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 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. "
arabic and english can be together in the same column and in the same field
below is a picture for an example database but this database collation is Arabic_CI_AS
but my problem is my original database created with latin collation and i want my Nvarchar columns store data like previous example
below is a picture for an example database but this database collation is Arabic_CI_AS
but my problem is my original database created with latin collation and i want my Nvarchar columns store data like previous example
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?
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
You can always try to change it by code:
If you want to know all the collations that exists in your SQL Server instance, run the following command:
ALTER TABLE [Product].[ProductCategory]
ALTER COLUMN Name NVARCHAR(20) COLLATE Arabic_CI_AS NOT NULL
If you want to know all the collations that exists in your SQL Server instance, run the following command:
SELECT *
FROM sys.fn_helpcollations()
Now I see. You have a constraint on that column, meaning that you need to drop and create the constraint as well:
ALTER TABLE Product].[ProductCategory]
DROP CONSTRAINT C_ProductCategoryName
ALTER TABLE [Product].[ProductCategory]
ALTER COLUMN Name NVARCHAR(20) COLLATE Arabic_CI_AS NOT NULL
CONSTRAINT C_ProductCategoryName CHECK
((LEN([Name])BETWEEN 1 AND 20) AND ([Name]+'.'<>'.') AND ([Name] NOT LIKE' %') AND ([Name]NOT LIKE'% '))
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'% '))
);
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'تيشيرت'
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
Open in new window