BCP & formatfile

[aitai]

Hi, All

I hope someone can give me insight into this problem-thanks in advance!

Text File with sample data:
CallDateTime,AnsweredDuration
08/02/2002 14:09:56,214
08/02/2002 14:09:54,9
08/02/2002 14:09:54,0
08/02/2002 14:09:52,0
08/02/2002 14:09:52,183

The bcp code:
bulk insert DailyCalls
from 'c:\inetpub\

http://wwwroot\mezun\test\xml\cf5soxml\DailyCall.txt'

with
(
firstrow = 2,
formatfile = 'c:\inetpub\

http://wwwroot\mezun\test\xml\cf5soxml\bcp_xml.fmt'

)

The formatfile:
7.0
2
1 sqldatetime 0 8 "," 2 CallDateTime
2 sqlint 0 4 "\r\n" 3 AnsweredDuration

The error:
Could not bulk insert. Invalid data type for column number 1 in format file

Much appreciate.

 

[arrowhouse]

How is your DailyCalls table defined?
How was your format file created & has it been edited?

I think the format file will need to read:

7.0
2
1 sqldatetime 0 19 "," 1 CallDateTime
2 sqlint 0 4 "\r\n" 2 AnsweredDuration

 

[aitai]

Hi, All

The solution to this question is below.

I reverse generated a format file from the dos command promt, the results are displayed below:

7.0
2
1 SQLCHAR 0 16 "," 1 CallDateTime
2 SQLCHAR 0 4 "\r\n" 2 AnsweredDuration

Whats happening is that the file is in character format in which case it takes 16 characters to store date and time which is why sqlchar works as a datatype. The sqldatetime format in the first format file expects the date in 8 bytes in sql server native format