Importing access files into SQL, all fields a nvarchar?

Hello,

 I have a question :

 I am importing an older Access 97 database into SQL 2k/2k5 (I've tried both), and when I import (using the sql server management studio) it wants to convert ALL character types (char, number etc) into nvarchar, whereas I want it to convert to varchar2.  I can't seem to find out WHY it is defaulting to nvarchar.  Besides editing every single column reference in the import tool (well over 1000 total columns across 140 tables), is there a setting I can set to force the
import tool to use varchar2 instead (ie: I don't want to use ANY n types at all).
 
 The reason for this is because once I have the database in SQL2k5 I need to run two queries, to convert all table and column names to full uppercase, and then I need to export the database directly into an oracle database.  If the column definitions stay as n types, oracle uses NLS for character conversions and it royally screw things up for some software.  So simple solution: use varchar2.

 Thanks in advance,

 Privasoft
privasoftAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
From BOL:
The SQL Server Import and Export Wizard uses the mapping files that Integration Services provides to map data types from one database version or system to another. For example, it can map from SQL Server 2000 to SQL Server 2005, or from SQL Server 2005 to Oracle. The mapping files in XML format are installed by default to C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles. If your business requires different mappings between data types, you can update the mappings to affect the mappings that the wizard performs. For example, if you want the SQL Server nchar data type to map to the DB2 GRAPHIC data type instead of the DB2 VARGRAPHIC data type when transferring data from SQL Server to DB2, you change the nchar mapping in the SqlClientToIBMDB2.xml mapping file to use GRAPHIC instead of VARGRAPHIC.

ref: http://msdn.microsoft.com/en-us/library/ms141209.aspx


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
privasoftAuthor Commented:
Good grief thanks a TON for this. This completely helped my solve several issues.

 Cheers
0
appariCommented:
Just a coincidence.
Today morning only I was searching on the ways to change this default behavior and came across this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.