October 3, 2011 at 8:03 pm
Hi,
I need to move double-byte data(multi-language) from Memo field in Access database to nvarchar(max) field in SQL Server 2005 database.
I wrote a routine for this using C# in Visual Studio. If I directly transfer the data, all I see in SQL Server is "???????" when there are non-English characters.
How can I transfer the data so that I see actual characters and not ???. Do I need to convert the encoding?
Thanks!
October 3, 2011 at 10:28 pm
Hi,
Check whether you've taken care of unicode characters from c# code.
You can do the same from Import and Export Wizard from Sql Server.
-Ami
October 5, 2011 at 8:29 am
Access Memo fields are equivalent to SQL nText data type. If I were doing this manually, I would bring it in to an nText and then copy the data to nvarchar. If I were using a package I would use the Data Conversion or Derived Column task.
--
JimFive
October 5, 2011 at 12:29 pm
James Goodwin (10/5/2011)
Access Memo fields are equivalent to SQL nText data type. If I were doing this manually, I would bring it in to an nText and then copy the data to nvarchar. If I were using a package I would use the Data Conversion or Derived Column task.--
JimFive
Of course text and ntext are deprecated and will no longer be supported. It is advised to NOT use them, especially in new development.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 1:19 am
Hi James,
Thanks for your reply. But, I agree with Sean. Nevertheless, I implemented your suggestion. Inserting non-English characters from Memo field to nText field still inserts ??? in SQL Server database instead of the actual characters.
Is there anything else I can try?
October 6, 2011 at 8:08 am
khushboo.dudani (10/6/2011)
Hi James,Thanks for your reply. But, I agree with Sean. Nevertheless, I implemented your suggestion. Inserting non-English characters from Memo field to nText field still inserts ??? in SQL Server database instead of the actual characters.
Is there anything else I can try?
Been awhile since I had to import from Access but I just set up a memo field in Access with some non-english characters like š¢ž etc. Then I imported that data using the import data wizard and it worked perfectly.
What do you mean when you say you have ??? instead of the characters? What are you using to view your data? The query window in SSMS displays pretty much any character that I have encountered. I have a feeling your data is fine but whatever you are using to view it is unable to display non-english characters.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 8:36 pm
Hi Sean,
Which version of Access are you using?
I have to transfer data from Access 97, where Memo field uses Double-byte encoding. If you are using recent versions of Access, then your Memo field must be using Unicode, in which case there should be no problem in importing non-English characters to SQL Server's nvarchar field, which recognises Unicode characters.
October 7, 2011 at 7:21 am
WOW that is a seriously old version. Maybe you can export your data to file first and then import to sql? I don't know that I can be much help. I haven't had Access 97 installed in a long long time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2011 at 9:00 pm
No problem Sean!
Thank you for taking some time out for this!
October 10, 2011 at 7:27 am
Let us know if figure out how to do this. Other people will find this via google or such in the future and would be nice to let them know how you resolve it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 10, 2011 at 7:32 am
I have to transfer data from Access 97, where Memo field uses Double-byte encoding. If you are using recent versions of Access, then your Memo field must be using Unicode, in which case there should be no problem in importing non-English characters to SQL Server's nvarchar field
Do you have a newer version of Access available? You could try making a copy of the database, converting to the newer version format and then importing into SQL.
--
JimFive
October 11, 2011 at 7:41 pm
I solved this issue by converting the encoding in my C# page as follows:
//Define Windows 1252, Big5 and Unicode encodings
System.Text.Encoding enc1252 = System.Text.Encoding.GetEncoding(1252);
System.Text.Encoding encBig5 = System.Text.Encoding.GetEncoding(950);
System.Text.Encoding encUTF16 = System.Text.Encoding.Unicode;
byte[] arrByte1 = enc1252.GetBytes(note); //string to be converted
byte[] arrByte2 = System.Text.Encoding.Convert(encBig5, encUTF16, arrByte1);
string convertedText = encUTF16.GetString(arrByte2);
return convertedText;
It was just a matter of figuring out which encoding data in Memo field of Access database used.
Thank you all for pitching in!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply