[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2069
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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