convert a unicode sql database into a non-unicode

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?

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...  

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?

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

 

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.

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.

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

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.

OK.  

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

Like I said above... Please click on "Request Attention" above to get others that know more than I do to help.

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.

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.

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....

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)

I will try this tomorow in a test environment and let you know.  

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.

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.