July 11, 2006 at 9:52 am
Sometimes pressing Execute in SS Management Studio results in success. The table gets filled exactly how I want.
Sometimes I get the following error:
"Cannot bulk load. Invalid destination table column number for source column 1 in the format file \\server\e$\temp\fmtfile.fmt."
Here's the contents of the format file:
7.0
19
1 SQLCHAR 0 0 "" 0 PK
2 SQLCHAR 0 1000 "\t" 2 F01
3 SQLCHAR 0 1000 "\t" 3 F02
4 SQLCHAR 0 1000 "\t" 4 F03
5 SQLCHAR 0 1000 "\t" 5 F04
6 SQLCHAR 0 1000 "\t" 6 F05
7 SQLCHAR 0 1000 "\t" 7 F06
8 SQLCHAR 0 1000 "\t" 8 F07
9 SQLCHAR 0 1000 "\t" 9 F08
10 SQLCHAR 0 1000 "\t" 10 F09
11 SQLCHAR 0 1000 "\t" 11 F10
12 SQLCHAR 0 1000 "\t" 12 F11
13 SQLCHAR 0 1000 "\t" 13 F12
14 SQLCHAR 0 1000 "\t" 14 F13
15 SQLCHAR 0 1000 "\t" 15 F14
16 SQLCHAR 0 1000 "\t" 16 F15
17 SQLCHAR 0 1000 "\t" 17 F16
18 SQLCHAR 0 1000 "\t" 18 F17
19 SQLCHAR 0 1000 "\r\n" 19 F18
(I use the 7.0 version because I don't like to deal with colation)
The reason I have a 0 for the PK column is because that field is used as an identity field in the table.
Here's the code:
bulk
insert dbo.datatable
from '\\server\e$\temp\datafile.txt'
with
(formatfile='\\server\e$\temp\fmtfile.fmt')
If I hit Execute enough times, I don't get the error anymore(?!) Sometimes it runs right off the bat; sometimes I have to hit Execute a couple or several times. I can't count on it working the first time.
Does anybody have an idea on how what I could do so that it will always succeed the first time I run the code?
July 14, 2006 at 8:00 am
This was removed by the editor as SPAM
July 14, 2006 at 11:42 am
We are having the exact same problem on some of our machines but it runs okay on others. Have not been able to isolate problem.
July 14, 2006 at 4:58 pm
I solved the problem with the other machines getting the bcp error. It was because they were running a pre service pack 1 version of sql 2005.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply