May 12, 2008 at 12:30 pm
Hi,
I currently have a web application connected to a MSSQL 2005 database. We are signing some new clients to our web application who will be using a different language set. So were going have to change the database to Unicode? Anyone have any experience with this, or what are the steps implications involved?
Thanks.
d
May 12, 2008 at 1:33 pm
I did several times without any issue.
May 12, 2008 at 1:34 pm
did u have to change every data type? or is there an easier way to do this?
May 21, 2008 at 4:03 pm
Collation can be set at different levels, such as database-, table-, and column-level. It is all based on your desire.
May 22, 2008 at 9:27 am
Unless I'm missing something, changing the collation will not make all the data unicode it will just determine the sort order, case sensitivity and code page for non unicode data. I have not mass converted a bunch of datatypes, but seems to me you will need to generate all the ALTER TABLE commands to convert CHAR, VARCHAR and TEXT to NCHAR, NVARCHAR and NTEXT.
Good luck with this.
David
May 23, 2008 at 9:33 am
The database collation can remain the same. The N-prefixed datatypes are automatically UNICODE. See BOL.
So you will have to convert all CHAR and VARCHAR columns to NCHAR and NVARCHAR. Convert the TEXT columns to NVARCHAR(MAX) vs. NTEXT.
Other work:
- You will need to change all CHAR and VARCHAR parameters and variables in all stored procedures and functions as well.
- Anywhere you have CHAR(8000) or VARCHAR(8000) or character columns > 4,000 will now be reduced to a maximum of 4,000. See BOL. So this may require other changes in your code.
- Other application changes such as ADO parameter datatype specifications to ensure that the "wide" characters are being passed into or out of the database. If using .Net, then strings are automatically UNICODE.
- other changes to be determined.
Given the above, it will work. We're doing it with a worldwide SaaS application.
[comment]
An unfortunate oversight when the database and system was designed. I see too many postings on this site where this potential issue is being ignored. I.e., every string is defined as CHAR or VARCHAR and not N
[end comment]
May 23, 2008 at 11:03 am
wow guys........thanks for all the input.
I certainly know where to begin now with this project. I'm going to start next week on this. Should be smooth sailing....now....i hope now anyway...haha
thanks again.
May 23, 2008 at 11:13 am
You will need to change all CHAR and VARCHAR parameters and variables in all stored procedures and functions as well.
I'll add a "plug" for Oracle that maybe the Microsoft SQL Server developers will think of adding to the product.
If the database was Oracle and the PL/SQL code used the "type inheritance" (%TYPE) when the variables were defined, you wouldn't have to do the all of the manual work in the above referenced step.
You just perform the ALTER TABLE statements to change the column datatype, optionally re-compile all invalidated procedures and functions (they'd be recompiled the next time that they were referenced anyway), and you're done!
May 28, 2008 at 12:40 pm
Check out Microsoft's SQL Server Best Practices web site, there are some papers on changing data types to Unicode.
http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
David
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply