data stored like ????? in sql server column

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

database collation is sql_latin1_general_cp1_ci_as

note that when i tried to enter data from table wizard edit top... data entered successfully in arabic
but when i insert it with insert statement it entered as ?????? even when put N prefix

Does your insert statement look like this?

INSERT INTO MemberAddress VALUES (N'شااارع',N'شاارع',N'شارع')

Is this column storing arabic exclusively? Maybe you could try creating your table with collation specified on that column?

DECLARE TABLE MemberAddress
(
ID IDENTITY PRIMARY KEY,
Address NVARCHAR(50) COLLATE Arabic_CI_AI_KS_WS
)

Actually, I don't think you should have to specify collation since you are using NVARCHAR.

ASKER

i installed windows and sql server ,may this error from error in installatin ?
however i installed the same version of windows and sql server before many times and this error didn't happened ever to me
i always when declare column as Nvarchar i could enter arabic data or english without N prefix
data entered successfully when i use table wizard update but by code ????

It does seem like maybe it's related to some incompatibility between client and server (even though they are one in the same in this case). I wonder if you could rule some things out by trying some tests?

1. Can you connect from this new machine to another database on another server where you know this has worked in the past? If it works in this scenario, I would guess that there is something wrong with the way you set up your new SQL Server database.

2. Can you connect from another machine where this has worked in the past to this new database server? If it works in this scenario, it could indicate a problem with the way you configured Windows on this new server.

ASKER

MR VanDongen
-----------------------
1- i repair my sql server and the repair ended successfully then,
2-i create another database with default collation (sql_latin1_general_cp1_ci_as) with one table one column nvarchar and the same error exists and when try to change collation
to arabic error that i cant change database collation.
3-i create another database with ( arabic 100_cs_ai_ks ) collation and it works successfully when i entered data in arabic and english
and then,
4- i tried to change my original database to this collation from (sql_latin1_general_cp1_ci_as) and this give me the error
can not change database collatin because --->
show me all constrait i did and say that object bounded with database collation ???
note that i didnot bound any column with collation by code acctualy this is the first time i know about collation.

please i want a collation that supports any language if possible but (arabic and english is neccessary) and that collation can be bounded with my database without the previos error
--------------------
thanks for your great care

I believe that sql_latin1_general_cp1_ci_as should be able to support any unicode characters in your Nvarchar column. This may be something to do with the locale of the machine you are running the insert statement from. How exactly does your configuration look? I know you said you were running SQL Server 2012. Is this on a Windows Server 2012 machine, windows 2008 Server, etc.? And you are running your insert statement in SqlServer Enterprise Manager in a query window, or with some other tool on another machine? Can you check the locale of the database server, and whatever client you are connecting from and ensure they are the same? You mentioned that this has worked for you on other database servers. Can you compare your locale and collation settings to another server where it works? I'm sorry I can't be of much help other than asking lots of questions. Hopefully we are heading down the right path with the collation and locale settings.

Locale settings will be in "Language and Region" settings in the control panel.

ASKER

MR VanDongen i thank you very much for your great care

1-I run sql server on windows 7 service pack 1.

2-here i will upload two database with it's collation and the difference will show clearly
in (Expert) database with sql_latin1_general_cp1_ci_as there is the error

in (EXPERT3) data base with arabic collation there is no error

my (ORIGINAL DATABASE) like (EXPERT)

any outstanding issues here? it seems that the encoding issue has been resolved?

ASKER

why i must put N prefix while i declare the column Nvarchar ??
if you can see the defrence between EXPERT and EXPERT3 queries you will understand what i mean

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

ASKER

but i dont put N prefix in EXPERT3 and data entered successfully
iam always donot use N prefix while i declare the column Nvarchar and data entered successfully in arabic or english ?!

but i dont put N prefix in EXPERT3 and data entered successfully
iam always donot use N prefix while i declare the column Nvarchar and data entered successfully in arabic or english ?!

that's because you set the collation at database level.

ASKER

1-sql_latin1_general_cp1_ci_as is general collation that can support any language and this collation at database level in EXPERT database

SOLUTION