bcp error

Question

bcp error

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

halifaxdal SSCoach Points: 19741 More actions · Answer 1

So I'm guessing that we can't prefix the value "0x" onto the hexcodes representing hierarchial ids but that also looks to be what happened in your text file.

Your guess is right, after I removed the hierarchialid field, the import is successful.

My suggestion at this point is to bcp out your file first, and use that as a reference to bcp'ing back in data. Does that make any sense?

Thanks, I thought GUI is simpler

x SSC-Insane Points: 23660 More actions · Answer 2

x

SSC-Insane

Points: 23660

October 22, 2014 at 1:58 pm

#1754182

Awesome! Glad it worked!

halifaxdal SSCoach Points: 19741 More actions · Answer 3

Since the hierarchyid is causing the problem, I took further search on this type and was able to find a way to convert it into varchar and successfully import it back to SQL.

Here is what I did to generate the data with conversion:

convert(nvarchar(20),convert(varbinary(20),OrganizationNode,1),1) as NewOrganizationNode

So the script to generate the data is:

SELECT TOP 1000 [BusinessEntityID]

,[NationalIDNumber]

,[LoginID]

,convert(nvarchar(20),convert(varbinary(20),OrganizationNode,1),1) as NewOrganizationNode

,[OrganizationLevel]

,[JobTitle]

,[BirthDate]

,[MaritalStatus]

,[Gender]

,[HireDate]

,[SalariedFlag]

,[VacationHours]

,[SickLeaveHours]

,[CurrentFlag]

,[rowguid]

,[ModifiedDate]

FROM [AdventureWorks2008].[humanresources].[Employee]

bcp command:

bcp AdventureWorks2008.dbo.EmployeeNew in C:\Data\EmployeeDataCommaDelimitedWithHierachyID.csv -c -t, -S -T

This imported all 290 rows into the new table EmployeeNew that has NewOrganizationNode which is a varchar(50)

BTW: I didn't remove the last few extra rows shows (290 row(s) affected), the bcp throws EOF error but still 290 rows are imported.

Thank you for your patience, and thanks to everyone for reading the thread.

Again I want to say bcp is really a great tool, but some times it is made too handy that you have to spent time on each case.

Happy bcpping!

x SSC-Insane Points: 23660 More actions · Answer 4

Nice! I looked more into your convert statement and the second part that finally converts to nvarchar, style '2' ditches the 0x! I've never used the style beyond datetime so thats handy to know!