?
Solved

convert a unicode sql database into a non-unicode

Posted on 2011-10-07
20
Medium Priority
?
356 Views
Last Modified: 2012-05-12
We need to convert a unicode SQL database into a non-unicode one.  Except droping manually everything (tables, constraints and indexes) and recreate them is ther any other methode out there?
0
Comment
Question by:MikeTa
  • 7
  • 6
  • 3
  • +1
18 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36932805
If you have Unicode encoded characters in your database, changing the correlation can cause them to be interpreted wrong.  Changing the correlation will not convert the characters in the data to the new new encoding.  If all of your data is in 7-bit ASCII, then it won't matter because the 7-bit base for all Western encodings and Unicode is 7-bit ASCII.

What are you trying to accomplish?
0
 

Author Comment

by:MikeTa
ID: 36932869
We have an ERP that uses a SQL database.  Couple years ago when it was first installed, the implementer did by mistake installed as unicode.  

Everything was OK until now when we are in process to upgrade the ERP to a newer version.  

The rutine that is doing the upgrade was not developped for the Unicode version of the program and therefore a lot should be done manually.  

As we will go live we have only a weekend window to upgrade the live database and the actual process is taking too long...  
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36932967
I think you just confused me.  Please click on "Request Attention" above to get others to help.

What ERP program?  What encoding is it expecting?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:MikeTa
ID: 36933114
Sorry, it wasn't my intention... You ask what I want to accopmplish:

We upgrade our ERP application and the SQL database schema that is part of it should be changed.

The automatic routine that is doing this works only with non-unicode therefore I look for a way to convert from unicode into non-unicode.  

Is there a program or someting that you know of that can do this

 

0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36933605
Then I'll have to ask the question again.  What encoding is it expecting?  You can't just make it non-Unicode.  You have to change it to something else.  The databases always have some correlation or character set.

It might be easier to save the current data and let the upgrade create a new database with the correct settings.  Then maybe import the old data back in with a script.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36933651
Well, I might be wrong.  This page http://msdn.microsoft.com/en-us/library/ms144250.aspx says there are versions of SQL Server that do not use collations.
0
 

Author Comment

by:MikeTa
ID: 36933677
the actual collation is SQL_Latin1_General_CP1_CI_AS and the non-unicode db can remain the same or have the default one for SQL 2008
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36933769
SQL_Latin1_General_CP1_CI_AS is not Unicode.  ??  This Microsoft expert says here http://blogs.msdn.com/b/qingsongyao/archive/2011/04/04/do-not-alter-database-collation-in-your-server.aspx to never change the collation.  Make a new database instead.

And beware of export/import data that includes collations.
0
 

Author Comment

by:MikeTa
ID: 36933806
OK.  

What script allow to bulk import all the data from the old db into the new one? Should I use a DTS?
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36934191
Like I said above... Please click on "Request Attention" above to get others that know more than I do to help.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36957420
The UNICODE vs NON-UNICODE data in SQL is reflected by the data type in the tables/columns where N in front of the data type stands for UNICODE. In your case you would have to issue a ALTER TABLE ... ALTER COLUMN on each "text" like columns to chnage them from ntext to text or nvarchar to varchar and so on. This may be challanging considering indexes and constraints besides the actual data stored in those columns that IS already UNICODE. For that type of data you would have to use BCP IN/OUT or bulk insert to ship data out into a file per text column, change the type of the file from UNICODE to ANSI - ASCII and reimport but CAUTION that some UNICODE characters could be lost.
Another way would be to create a new identical database with NON-UNICODE data types without any indexes/constraints and try export/import all data into the new DB by using SSIS.
SQL COLLATION can stay the same and will not impact your transfer but please remember - UNICODE char encoding is 16bits vs NON-UNICODE -ASCII or UTF8 which is only 8bits therefore the potential issues ancountered.

http://metrix.fcny.org/wiki/display/dev/Tips+on+Choosing+SQL+Server+Data+Types
http://msdn.microsoft.com/en-us/library/ms190252.aspx.
0
 
LVL 12

Expert Comment

by:Chris M
ID: 36957431
Hi,
First, create a new database with the right settings for your new version of the ERP, then import the data from your old database (one table at a time) into the newly created database.

If your new database has relationships before importing the data then do not use a generic script to import all objects from the old database since the script might not be "wise" enough to consider relationships between objects.

There are serious implications where related data is messed up, partly imported or not correctly imported.
It is best to study the relationships between your objects and derive a sequence (order) of importing your data.

If however there are thousands of tables in your old database, you might want want to use a script to import your data but if relationships are enforced on the new database then ensure that it follows the sequence of relationships you derived (as I discussed above) otherwise remove relationships to perform the import exercise faster and enforce relationships after.

Besides a script, you can use DTS, SSIS, Import/Export wizard or BCP if you want.

I must remind you that in case the destination database already has relationships enforced before the import, import the data in the order of relationships (non-referenced data first, primary data second & then relating data is comes last).

The easiest way rather is to create the new database without relationships, import all tables & data first, create relationships (enforce integrity), then views and other objects come in after that.

If you script the old database, ensure that the correct collation is used in the scripts by either editing the script or simply eliminating the settings so that the scripts use the default collation specified in the new database.

All the best, let me know how it goes.
0
 

Author Comment

by:MikeTa
ID: 36957667
the unicode and non-unicode database are identical with the only diffrence tha on has the N.. and the other hasn't.  

I can easily create a new empty non-unicode  database just by installing the ERP program. They have the same collation.

The problem now is to transfer all the data from the unicode one into the non-unicode one and to populate all ~500 tables properly.  Something similar to restoring from a backup....
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36957817
If you restore from backups taken against UNICODE DB then the data type will be UNICODE and you don't want that.

If you have an empty database NON-UNICODE then I suggest you try a EXPORT from the UNICODE to NON-UNICODE by using the SQL Wizard for all the tables and if you are lucky (only non-unicode data exists in your source tables) then it may work - just right click the DB name in SSMS and select Tasks then Export Data.

Caution to not perform this action during production as the data transfer may (severely) impact performance on that server (especialy if bothe db's are on the same SQL)
0
 

Author Comment

by:MikeTa
ID: 36957959
I will try this tomorow in a test environment and let you know.  
0
 
LVL 12

Expert Comment

by:Chris M
ID: 36961036
Mike, please understand the challenge you have: moving data across databases with different collations.
Like I stated earlier, you will have to export the data from the old DB and import it into the new DB using any of the tools I listed by choosing what you prefer to use.

Import/export wizard can be cool.
Since you create a new database is by installing the new ERP, you might have to take note of the relationships in the new database, drop the relationships, import the data by mapping the old database's objects to the new DB objects, then recreate the relationships after importation.

Do this by scripting the new database after creating it, drop only the relationships & triggers (if any), before importing the data.

All the best.
0
 

Author Comment

by:MikeTa
ID: 36979437
It is not working in my case because the ERP works native on Progress DB and it has a conversion layer to accomodate MS SQL.  All relations between tables, triggers and indexes are treated as being in Progress.  
0
 
LVL 12

Accepted Solution

by:
Chris M earned 2000 total points
ID: 36979678
Let's focus on your SQL server database for now.
Once the new SQL database is created by installing your ERP, here's what you're going to do following the steps below:

1. Backup your new database.
   *Quick one: you could just shut down SQL server, copy the mdf, ldfs somewhere, then start SQL again.
2. Script out all relationships between the tables.
    *You do not need to script out procedures, functions or indexes.
3. Disable all triggers. - very important
4. Drop all relationships between tables.
5. Import data from old database into new database.
6. Re-create your relationships back using the scripts made earlier.
7. Enable your triggers.

Let me know where you fail.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question