unicode conversion

I have a sqlserver 2008 database that is currently using varchars.  I need 
to move this to unicode.

What is the best way to accomplish this task?  Should I recreate the 
database using a certain character set (like utf-8) and then migrate the 
data or do I just do an alter table and move the fields from varchars to 
nvarchars?

I am not real familiar with best practices for SQL Server and unicode.

Thanks 


0
Joel
4/19/2010 3:59:15 PM
sqlserver.server 1327 articles. 0 followers. Follow

1 Replies
795 Views

Similar Articles

[PageSpeed] 25

Joel (nowhere@nowhere.com) writes:
> I have a sqlserver 2008 database that is currently using varchars.  I need 
> to move this to unicode.
> 
> What is the best way to accomplish this task?  Should I recreate the 
> database using a certain character set (like utf-8) and then migrate the 
> data or do I just do an alter table and move the fields from varchars to 
> nvarchars?
 
This is a fairly complex operation, no matter how you do it - the moral is
that you should do it right from the beginning.

At first glance, ALTER TABLE ALTER COLUMN may seem to be the way to go,
but this command can only handle one column at a time, and since the
change requires a physical change to the table, that can hurt. Even more
problematic is that all indexes referring to the columns needs to be dropped 
first, and likewise PRIMARY KEY, UNIQUE and FOREIGN KEY constraints.
(Actually also CHECK and DEFAULT constraints.)

So it may be better to build a new database from scripts and copy data
over. But obviously that can take some time. And it is a difficult 
operation in itself.

As for the character conversion, that is no problem. If you just run INSERT
statements, SQL Server will convert characters as needed.

Note also, that you cannot store data as UTF-8 in SQL Server. SQL Server 
uses UCS-2 to store Unicode.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
4/19/2010 9:48:05 PM
Reply:

Similar Artilces:

Need a Days old to Date conversion
Hello, I have a number of days old that I need converted into the date tha the number originated from. For instance, If a unit is 390 days old what date was it received. Any help will be greatly appreciated. Thanks. Marshall Lewi -- ml@po ----------------------------------------------------------------------- ml@pok's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1622 View this thread: http://www.excelforum.com/showthread.php?threadid=27740 Marshall If the days old is in A1 then in B1 type =Now()-A1 This will need to be formatted as a date -- HTH Nick Hod...

Which control should I use to display Unicode characters?
Hi, there, I want to show Unicode characters, such as Chinese and Arabic. Which control should I use? Could you please give me a bit of example code? Thanks a lot. Gary You do not need specific control to display Unicode strings. Normally, you would use SetWindowText function to set the control text. If you look in winuser.h file this 'function' #defined as SetWindowTextA or SetWindowTextW (depending on the type of the build). If you want to use Unicode text regardless of build type, specify SetWindowTextW explicitly. Note that you need to have appropriate fonts installed to a...

unicode application
hi, Priyanka here, this is a question related to VB. i want to make the VB application unicode based. i am not able to do it right now. if anybody knows please let me know about this. ...

VERIFY and TRACE, how to implement for debug/release, unicode-aware, no warnings at lvl 4
Hi group and apologize because this isn't a pure MFC-question. I have to finish a raw win32 app written in C++. I really miss the macros TRACE and VERIFY, which I tend to use when writing MFC programs. I googled for "win32 verify macro" and the very first hit is for a codeproject.com project with implementations to be used in a win32 program, however. The project contains a single header file, debug.h, but it doesn't compile without warnings (using warning level 4), doesn't seem to be unicode aware and doesn't compile at all in release mode. I was just wonderin...

Unicode
I want to make an application for international use which should all languages(which unicode support). So i want to know how to change language? or in ther words, how to change my Locale( at run time). ...

How to convert to unicode in ADSI
Hi all, I made a vbs to add an extra e-maill address to a distribution list group. The e-mail address should be <group name>@dnv.com. As we are a Norwegian company, the group name sometimes contains Norwegian character such as "Ø". When I look into ADSI, the string with "Ø" do updates in "mail" attribute. But if I want to send e-mail to this distribution goup, error will report that "The format of the e-mail address is incorrect". I found if I manually create the e-mail address, the system will automatically convert ...

Conversion from MS Works
I have some MS Works spreadsheet files saved to disk. My new computer does not have MS Works, but does have Excel. I cannot open the MSWorks spreadsheets in order to copy and paste them into Excel so I can work with them. Any ideas? Is there any such thing as a MS Works spreadsheet viewer? I have not been able to find one. Thanks for any help you can give me. You can download the Office conversion tools from the Office Update web-site to read any MS-Works files. >-----Original Message----- >I have some MS Works spreadsheet files saved to disk. My >new computer does not ...

ftpd unicode
In Wince 5.0 file and folder names may contain UNICODE (e.g. cyrillic or chinese)characters. I was not able to transfer such files via FTP. I think it is a problem of ftpd. Any suggestions ? Tank you You've got full source code of FTPD, so clone it (see my blog) and change it to whatever you need, or buy a 3rd party FTP implementation for CE. Note that FTPD is shipped as "Sample" meaning it's not a full featured final program. Good luck, Michel Verhagen, eMVP Check out my blog: http://GuruCE.com/blog GuruCE Microsoft Embedded Partner http://...

Clipboard for Unicode and Non-Unicode
Hi all, We have a progarm write with non-unicode. I found that in some language, the character can not be pasted to CEdit correctly. For example, if our application is running on Russian XP, the string copy from IE can be pasted to CEdit properly. But if I pasted the same string into Notepad, and then copy the string from notepad to our CEdit, the string can not show corectly. Is it a coding problem? Is it possible to make CEdit to accept the string from notepad? Thanks, Justin If it is a question of pasting a UTF16 string into a single-byte edit control, you could try it in two stages: L...

PROBLEM WITH ACCESS 95 DATABASE CONVERSION
Hi. I have a problem converting an access 95 database to access 2002. When the conversion is completed, It makes a table with one error field. The error is: " FILE MSJETSQL.TLB NEEDS THE VBE REFERENCE OR IS ERRONEOUS " I tried with other access 95 databases and It happen in everyone. I tried in other computer with the same configuration and it works ok. What can I have to do?. Reinstall the Access 2002?. Search for the file msjetsql.tlb in disk?. Thanks. I've solved copying the file from other PC. "JP" <aspento_quitar_para_correo_@arrakis.es> escribi� en e...

How do you enter Unicode characters that don't have a Alt-nnnn shortcut?
I have a need to enter a range of non-English names into a combo box on a form in an Access 2003 application. Sometimes this requires the use of accented characters that do not appear on a UK keyboard. If I refer to the Windows XP Character Map facility (charmap,.exe) then I can see that many such foreign characters have been allocated an Alt-nnnn code that is displayed at the bottom right of the Character Map acreen. This makes it easy to enter these characters. However, many other characters have not been given an Alt-nnnn keyboard shortcut. They only have a U-FFFF code, displayed at...

Opening CSV file(saved in unicode) in excel
Hi, If anyone can help me on this, I would really appreciate that. I have a file that is saved in .csv extension (Unicode format). Let's say for example, I have the data as follows: "text1","text2",123,"text3",450.00 When I save the above data in .csv (in Ascii format) and open in excel file, I see that each value is placed in adjacent columns. When I save the same data in .csv (Unicode) I see the whole information appears in the single cell(first cell only). Is there a limitation to the CSV FILE opening in EXCEL, with Unicode format? Any inputs/...

unicode format files for Outlook 2002
I use Outlook 2002. I would like to use Unicode files (for larger size). I see references to using it with Outlook 2003, but none for 2002. can unicode files be used with Outlook 2002? If so, how? thanks, Huck No OL 2003 & 2007 only "Huck Rorick" <huckrorick@groundwork.org> wrote in message news:epYUYAl4IHA.2348@TK2MSFTNGP06.phx.gbl... >I use Outlook 2002. I would like to use Unicode files (for larger size). >I see references to using it with Outlook 2003, but none for 2002. can >unicode files be used with Outlook 2002? If so, how? > > than...

Unicode problem
Given the following code snippet: WNetOpenEnum( dwScope, 0, 0, NULL, &hEnum ); struct hostent *host; DWORD Count = 0xFFFFFFFF; LPVOID Buffer = new char[16384]; DWORD BufferSize = 16384; WNetEnumResource(hEnum, &Count, Buffer, &BufferSize); NetResource = (NETRESOURCE *) Buffer; for (int i = 0; i < Count; i++, NetResource++) { CString strFullName = NetResource->lpRemoteName; ... host = gethostbyname((LPCTSTR) strFullName); } This works when I do *not* have _UNICODE and UNICODE defined. When I *do* define those directives, I get a compiler error on the call to the...

create csv which contains unicode
i want to create csv which contains unicode can anyone help me out Requirement is not clear. Use Excel to create the unicode content and then save as CSV or Unicode txt format. "archana" wrote: > i want to create csv which contains unicode > can anyone help me out ...

printing UNICODE and convert CString to UNICODE
1.how do i convert some CString to UNICODE ? ? ? i search in MSDN and i dont understand how to do it . 2. i writing some program and i need to send some CString to printing . in my CString there is some local language (not eanglish) and the printer always give me some GIBRISH data. pleace , someone can help me ?? ? tanks. For 1: Use MultiByteToWideChar() Or, Use a CStringW and initialise it from your CString. For 2: You will need to give more information about how you are getting your text to the printer. You should be getting a device context for the printer and use DrawText or a ...

View conversation thread in Outlook?
I am wondering if I can set up my Outlook to give me a feature which is standard with Goldmine email. I realize that GM is a database first and email client 2nd (or later), but I have been able in GM to call up all of an email conversation with a given contact with one or two clicks. Also, with Goldmine the emails are automatically numbered as the replies continue. The numbers, which I'm sure you are all familiar with, are within brackets at the beginning of the subject. This allows the user to keep track of which email came in what order without studying the date and time fields very...

Unicode Support
Is there a way to insert characters in Microsoft Office V.X Applications, such as Unicode 010D and 0117 (c with a caron above it and e with a dot above it)? They don't seem to appear in the Insert -> Symbol box, and I can't insert them from the OSX Character Palette. Thanks for your help! Dave Feucht Dave Feucht <davidf@cs.pdx.edu> wrote: HI Dave, > Is there a way to insert characters in Microsoft Office V.X > Applications, such as Unicode 010D and 0117 (c with a caron above it > and e with a dot above it)? They don't seem to appear in the Insert &g...

Excel Date Conversions #2
Hi- I am trying to sort a mailing list that is in Excel by date. The format of the date as exported by our mailing list program is 01-Apr-02. Excel knows that this is a date but cannot tell if it should convert to 19 or 20 based on if the number is from the 90's or 2000 and after. Is there any way to have Excel automatically insert the needed digits appropriately? Also, is it possible to then convert the formate to yyyy/mm/dd so that I can sort the list and eliminate all entries over 4 years old? My ulimate goal is only to remove entries over four years old so if anyone knows how to do ...

Handling UNICODE #2
Hello, again Here is another problem (at least for me). In the edit control non-English string is displayed properly. There is a member variable for this control, of a CString data type. My question is - how to assign this non-English string to this variable? I mean when I'm calling UpdateData(TRUE), in Debug the value for this member data is "??????" and also when I'm reading recordset, which returns this non-English value properly, (I can see it running corresponding stored procedure in Query Analyzer), the variable of a type _bstr_t, which receiving the value also look...

RegQueryValueEx and UNICODE.
Hello everybody. I Have a function to extract char data from the registry, but I don't know if it is UNICODE compliant, this is my code: BOOL CRegistry::Read (LPCTSTR pszKey, CString& sVal) { ASSERT(pszKey); DWORD dwType; DWORD dwSize; LONG lReturn; //m_hKey already is ok: lReturn = ::RegQueryValueEx (m_hKey, pszKey, NULL, NULL, NULL, &dwSize); if (lReturn != ERROR_SUCCESS) return FALSE; //Is this UNICODE compliant? char *pszChain = (char*)malloc(dwSize + 1); if (!pszChain) return FALSE; dwSize++; lReturn = ::RegQueryValueEx (m_hKey, pszKey, NULL, &dwTyp...

Non-unicode version of LsaOpenPolicy...
The LsaOpenPolicy function takes Unicode string as parameter. Is there a non-Unicode version of the same function available..? Changing the entire application for Unicode support is very difficult. Even if there is a workaround, please let me know..? Thanks in advance. regards, Anand G Anand G wrote: > The LsaOpenPolicy function takes Unicode string as parameter. Is there a > non-Unicode version of the same function available..? > Changing the entire application for Unicode support is very difficult. Use Unicode string just for this function. Instead of CString use CStringW,...

Convert UTF-16 Unicode to UTF-8 Unicode?
How can I convert a UTF-16 (also known as UTF-7) unicode text file to a UTF-8 unicode text file? Thanks, Greg Open file with notepad, File - Save as choose the name of new file and encoding UTF-8 Then button "Save". Or you like do this from vb code? www.coin-masters.com "Greg Lovern" <gregl@gregl.net> wrote in message news:29051aaf-cf9f-44f9-838f-21952a243bcb@x3g2000yqd.googlegroups.com... > How can I convert a UTF-16 (also known as UTF-7) unicode text file to > a UTF-8 unicode text file? > > > Thanks, > > Greg ...

MS assumes everything is Unicode with VS 2008?
Any idea why, with VS 2008, MS changed CRichEditCtrl::GetSelText() to return an LPTSTR instead of LPSTR? This is a breaking change for me, but not the only one. Thanks Mike mike.frith@gmail.com wrote: > Any idea why, with VS 2008, MS changed CRichEditCtrl::GetSelText() > to return an LPTSTR instead of LPSTR? This is a breaking change for > me, but not the only one. > They will of course be the same, if you remove the default "Use Unicode" setting from the project. Bo Persson <mike.frith@gmail.com> wrote in message news:baa2952d-d165-45f0-a1cd-ba4d1fce53c...

Quicken 2002 Conversion
I've downloaded the trial version of Money 2004 and I'm trying to convert Quicken 2002. So far I've been unable to do this. At the beginning of running Money, it prompted me to convert my exiting Quicken file. Since I didn't want my original existing file converted, but rather a backup file, I bypassed this screen. I thought I could search for the appropriate Quicken file and then convert, but I haven't found a way to do that yet. I don't want my original Quicken file disturbed in any way because I'm still running Quicken. Any suggestions? Thanks. In micr...