Solved

SQL 2005 ODBC Connection Support

Posted on 2006-06-29
6
2,044 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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now