Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server Import/Export Wizard data conversion component

Posted on 2015-01-07
2
Medium Priority
?
5,211 Views
Last Modified: 2015-01-09
Hi all, I have a table in a SQL Server 2008 Database which has a column called AltIndustries in nvarchar(max) format.  I ran into an error while trying to export this table to a tab-delimited text file and receive the error:

"Error 0xc020802e: Data Flow Task 1: The data type for "input column "AltIndustries" (301)" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.
 (SQL Server Import and Export Wizard)"

A couple of things:  
The column is the output of a company function that defines its output as nvarchar(max) so that is working as expected.  

Apparently there's a data conversion component to the Import/Export wizard which I'm unaware of.  Does anyone know how I can install this component and use it?

I was able to get around the issue this time by CASTing the output of the function as CHAR(2000) but would like to gain greater understanding about the solution given in the error message.  I expect that I will be running into this again and the output then may be longer.  I'm trying to find a solution where I don't have to test the output length and modify code to insure the cast has enough space.

Comments?

Thanks, Rich
0
Comment
Question by:RichNH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 2000 total points
ID: 40536556
First - Did you try checking the Unicode box on the Choose a Destination box in the export wizard? That would mean that you are not exporting to an ANSI file, but a Unicode file.

If that doesn't work for you, The simplest way to perform the data conversion is in the source specification.  Instead of just choosing "Copy data from one or more tables or views", choose "Write a query to specify the data to transfer". The create a SELECT statement on the source table, and when you enter the AltIndustries column, put it in a CAST statement - CAST(AltIndustries as varchar(MAX)) as AltIndustries

If for some reason neither of these options work, you might have to create a view on the source with the conversion in the view. However, both of the above options SHOULD work for you.
2
 
LVL 1

Author Closing Comment

by:RichNH
ID: 40541011
The Unicode didn't work but the VARCHAR(MAX) did when I tested it.   Thank you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

610 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