Solved

SQL 2005 ODBC Connection Support

Posted on 2006-06-29
6
2,053 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 42

Accepted Solution

by:
Eugene Z 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 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