Importing access files into SQL, all fields a nvarchar?


 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,

Who is Participating?
appariConnect With a Mentor Commented:
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.


privasoftAuthor Commented:
Good grief thanks a TON for this. This completely helped my solve several issues.

Just a coincidence.
Today morning only I was searching on the ways to change this default behavior and came across this.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.