SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Datatype Change


Datatype Change

Author
Message
VastSQL
VastSQL
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33049 Visits: 5927
Changed one of the existing column datatype from varchar(250) to nvarchar(250) but the column still showing arabic characters as ??

How can i resolve this?
sgmunson
sgmunson
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98483 Visits: 7265
We need a lot more detail. It appears likely that the data you have is not standard English characters, but just changing a datatype from varchar to nvarchar only provides the means to store unicode characters. As the column was varchar at the time it was populated, if there were unicode characters being used, then such characters were likely converted, possibly implicitly, at the time the data was inserted or updated into the table, and one of the possible consequences is that information got lost in that conversion. You'll need to provide more specific details and as much specific history of events as you can in order for someone to be able to help much. One other possibility is that you just have foreign language characters, and you may just need to set the proper collation for that column, but that seems a lot less likely.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)

Group: General Forum Members
Points: 166033 Visits: 22830
VastSQL - Monday, March 5, 2018 5:42 AM
Changed one of the existing column datatype from varchar(250) to nvarchar(250) but the column still showing arabic characters as ??

How can i resolve this?


As Steve mentioned, once the data is saved or converted into varchar, there's no way to get the unicode data back.
If after the data type change, you still can't store the arabic characters correctly, you need to check your string handling. Here's an example of what I mean.

DECLARE @Test TABLE( MyString nvarchar(250));

INSERT INTO @Test VALUES('طالفٱئذش'); --Random string without any knowledge on what it means
INSERT INTO @Test VALUES(N'طالفٱئذش');

SELECT *
FROM @Test;



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
VastSQL
VastSQL
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33049 Visits: 5927
Luis Cazares - Monday, March 5, 2018 6:33 AM
VastSQL - Monday, March 5, 2018 5:42 AM
Changed one of the existing column datatype from varchar(250) to nvarchar(250) but the column still showing arabic characters as ??

How can i resolve this?


As Steve mentioned, once the data is saved or converted into varchar, there's no way to get the unicode data back.
If after the data type change, you still can't store the arabic characters correctly, you need to check your string handling. Here's an example of what I mean.

DECLARE @Test TABLE( MyString nvarchar(250));

INSERT INTO @Test VALUES('طالفٱئذش'); --Random string without any knowledge on what it means
INSERT INTO @Test VALUES(N'طالفٱئذش');

SELECT *
FROM @Test;

Thanks Luis,

Data get inserted using Linq query and we tried creating a new nvarchar column inserted into both column the same value at once and the new column is populating data properly but the column we changed from varchar to nvarchar is still showing data as ??

Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)

Group: General Forum Members
Points: 166033 Visits: 22830
VastSQL - Tuesday, March 6, 2018 3:08 AM

Thanks Luis,

Data get inserted using Linq query and we tried creating a new nvarchar column inserted into both column the same value at once and the new column is populating data properly but the column we changed from varchar to nvarchar is still showing data as ??

If it's data that was already in the table before the data type change, then it's lost. You would need to correct it as there's nothing stored that would allow you to identify which were the characters that you intended to store in the first place.



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)

Group: General Forum Members
Points: 401499 Visits: 43429
VastSQL - Monday, March 5, 2018 5:42 AM
Changed one of the existing column datatype from varchar(250) to nvarchar(250) but the column still showing arabic characters as ??

How can i resolve this?


Once the Unicode data was converted to non Unicode data and stored in the varchar(250) column the data is lost. Converting the column to nvarchar(250) will not recover the data. You will have to go back to the original source to recover the data.

Edit: Should have read further, sorry for repeating what has already been said.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
VastSQL
VastSQL
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33049 Visits: 5927
Lynn Pettis - Tuesday, March 6, 2018 8:44 AM
VastSQL - Monday, March 5, 2018 5:42 AM
Changed one of the existing column datatype from varchar(250) to nvarchar(250) but the column still showing arabic characters as ??

How can i resolve this?


Once the Unicode data was converted to non Unicode data and stored in the varchar(250) column the data is lost. Converting the column to nvarchar(250) will not recover the data. You will have to go back to the original source to recover the data.

Edit: Should have read further, sorry for repeating what has already been said.
Thanks Lynn
I am talking about new data being inserted after changing the data type

GilaMonster
GilaMonster
SSC Guru
SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)

Group: General Forum Members
Points: 919842 Visits: 48856
Look at your LINQ. Is it still mapping the column as ascii text?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


VastSQL
VastSQL
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33049 Visits: 5927
GilaMonster - Wednesday, March 7, 2018 1:03 AM
Look at your LINQ. Is it still mapping the column as ascii text?

Thanks Gail.

Finally find the issue. In Db context the unicode was set to false. Developer changed it to True and now its working as expected.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search