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
Solved

SQL 2005 ODBC Connection Support

Posted on 2006-06-29
6
2,052 Views
Last Modified: 2012-06-21
I recently installed SQL 2005 Developer Edition, and have found that ODBC access is only supported via ADO.net wrapper.  I'm running into a error importing
text fields. I am able to import integers from the same tables no problem.

TITLE: SQL Server Import and Export Wizard
------------------------------

Could not connect source component.

Error 0xc0204016: DTS.Pipeline: The "output column "ItemNumber"
(12)" has a length that is not valid. The length must be between 0 and 4000.

For some reason it's seeing the field as varchar, and I can't work around it
(tried assigning field as text, vtext, use substring to trunc import data all
with no luck).

What am I missing ?

Thanks in Advance,

Erika
0
Comment
Question by:rikkirik
6 Comments
 
LVL 12

Expert Comment

by:Einstine98
ID: 17015890
are you getting the wizard to create the table at the other end?
0
 
LVL 12

Expert Comment

by:ill
ID: 17016037
did you try as nvarchar(4000) ?
0
 

Author Comment

by:rikkirik
ID: 17016160
Hi there,

Details of what happens in wizard:

1.Query to specify data to transfer is

SELECT ItemNumber
FROM Items

2.Then the wizard moves on to selecting and editing query mapping. When you go to edit the Column Mapping the data type defaults to nvarchar Precision 30.  Source column ItemNumber shows as VARCHAR(30). Changing to nvarchar(4000) or to any other data type and size doesn't appear to have any effect on error message.

The autogenerated create sql table statement begins as this:
CREATE TABLE [TestDB].[dbo].[Query] (
[ItemNumber] nvarchar(1))


3. Then as you complete the wizard the Error 0xc0204016 message appears.

Thanks Erika

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 17017759
did you install  sp1 for sql server 2005 ?
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17020240
try this (just to make sure)

SELECT CAST (ItemNumber as int)
FROM ITEMS

do you have the same problem?
0
 

Author Comment

by:rikkirik
ID: 17028561
I tried
SELECT CAST (ItemNumber as int)
FROM ITEMS

but sql statement did not parse.  

Updated to SP1 which stopped this particular error from occuring, but process was still failing during debugging due to data type conversion. Imported ntext columns will not convert to nvarchar(10).

I fixed this issue by using substring function

SELECT SUBSTRING(ItemName,1, 10), ItemName, CustomField3, CustomList2ID, CustomListText
FROM Items, CustomLists
WHERE ItemNumber <015999 AND CustomList2ID = CustomListID

Conversion from ntext to nvarchar(10) was then successful

Thanks  
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

792 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