qinyan
asked on
OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].COLUMN014'
OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].
I got this message when I DTS an Excel file to a database table (the datatype for the destination column is float). I tested by removing that column and it was ok. I saved the file as csv and loaded it and query the table using where isnumeric(column014) = 0 and column014 is not null I got nothing so there should be no invalid data in this column. Then why I got this error when loading from Excel. It didn't work even if I loaded from the temp table (which is loaded from the csv file so column datatype is varchar) to my destination table. It didn't work either when I used insert into destination table select * from temp table and also use explicit conversion to convert datatype of the column in temp table to the datatype of the column in destination table.
Has anyone had this problem before? if so how did you solve it? Thanks!
I got this message when I DTS an Excel file to a database table (the datatype for the destination column is float). I tested by removing that column and it was ok. I saved the file as csv and loaded it and query the table using where isnumeric(column014) = 0 and column014 is not null I got nothing so there should be no invalid data in this column. Then why I got this error when loading from Excel. It didn't work even if I loaded from the temp table (which is loaded from the csv file so column datatype is varchar) to my destination table. It didn't work either when I used insert into destination table select * from temp table and also use explicit conversion to convert datatype of the column in temp table to the datatype of the column in destination table.
Has anyone had this problem before? if so how did you solve it? Thanks!
ASKER
Yes I saw this link after searching the error message on google but I don't think this is the problem I'm experiencing. I'm using 2000 DTS not 2005 and I still get this error. and I checked the field in Excel it's just numbers not formula so there is no way for me to tell whether there is divide by zero issue or not. Plus, as I mentioned, I did load it ok after saving it as csv or txt file and ran a query and didn't find any records where isnumeric(columnxx) = 0.
Thanks!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Will try the suggestion when I get a chance; I already worked it out myself using a different way.
Try this link: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1003710&SiteID=1
It doesn't tells how to find the problem but it might point in right direction.
Hope this helps.