[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2075
  • Last Modified:

SQL 2005 ODBC Connection Support

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
rikkirik
Asked:
rikkirik
1 Solution
 
Einstine98Commented:
are you getting the wizard to create the table at the other end?
0
 
illCommented:
did you try as nvarchar(4000) ?
0
 
rikkirikAuthor Commented:
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
Eugene ZCommented:
did you install  sp1 for sql server 2005 ?
0
 
Einstine98Commented:
try this (just to make sure)

SELECT CAST (ItemNumber as int)
FROM ITEMS

do you have the same problem?
0
 
rikkirikAuthor Commented:
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now