Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial