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_AddProductCategory]
(@Name NVARCHAR(20))
AS
BEGIN
            INSERT INTO [Product].[ProductCategory] VALUES (@Name)
END

EXEC [Product].[SP_AddProductCategory] 'T-shirt' -----result--> T-shirt
EXEC [Product].[SP_AddProductCategory] 'تيشيرت' -----result--> ??????

i want to add N prefix in stored procedure to be able to stored arabic values successfully when exec the stored procedure
ebram ishaakMicrosoft SQL Server Developer and C# DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Try:
EXEC [Product].[SP_AddProductCategory] N'تيشيرت'

Open in new window

0
ebram ishaakMicrosoft SQL Server Developer and C# DeveloperAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Does the column has an Arabic collation?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

ebram ishaakMicrosoft SQL Server Developer and C# DeveloperAuthor Commented:
database collation is (sql_latin_general_cp1_ci_as)
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
1

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
ebram ishaakMicrosoft SQL Server Developer and C# DeveloperAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
ebram ishaakMicrosoft SQL Server Developer and C# DeveloperAuthor Commented:
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
error.JPG
what can i do in steps please?
thanks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
in my case user can store arabic or english values in the same column actually in the same field
Please 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_as) can support arabic and english?)
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, ...

-if yes, why data stored like ????? when store arabic
That'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 error
That'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. "
1
ebram ishaakMicrosoft SQL Server Developer and C# DeveloperAuthor Commented:
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
arabic-and-english-together.JPGbut my problem is my original database created with latin collation and i want my Nvarchar columns store data like previous example
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
ebram ishaakMicrosoft SQL Server Developer and C# DeveloperAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can always try to change it by code:
ALTER TABLE  [Product].[ProductCategory]
    ALTER COLUMN Name NVARCHAR(20) COLLATE Arabic_CI_AS NOT NULL

Open in new window


If you want to know all the collations that exists in your SQL Server instance, run the following command:
SELECT *
FROM sys.fn_helpcollations()

Open in new window

0
ebram ishaakMicrosoft SQL Server Developer and C# DeveloperAuthor Commented:
error.JPG
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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'% '))

Open in new window

Now, in the example from your last comment you have the CREATE TABLE statement as well. Why is that? Are you recreating the table? If so then you don't need to alter nothing and create the column with the Arabic collation immediately:
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'% '))
 );

Open in new window

0
ebram ishaakMicrosoft SQL Server Developer and C# DeveloperAuthor Commented:
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 !!!!!!!!
error.JPG
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You're not sending the parameter with N for nvarchar:
EXEC [Product].[SP_AddProductCategory] N'تيشيرت' 

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
ebram ishaak, is this issue solved or do you still have questions about it?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comments as solution.
0
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.