BCP problem - help?

I'm attempting to run bcp to copy some files into a database and
receiving a format file error (below). I'm using someone else's format
file that I know already works for them, and I don't know what the
issue is. Below is the error, my bcp statement and format file....can
anyone tell what the problem is? (Note - some of the Collation
values are wrapped b/c of my newsreader - in the format file they are
all on the appropriate line and tab delimited). Thanks.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
"c:\documents and settings\user\my
documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
8.0
44
1SQLCHAR01""1RTSQL_Latin1_General_CP1_CI_AS
2SQLCHAR04""2VERSIONNULL
3SQLCHAR010""3TLIDNULL
4SQLCHAR01""4SIDE1NULL
5SQLCHAR01""5SOURCESQL_Latin1_General_CP1_CI_AS
6SQLCHAR02""6FEDIRPSQL_Latin1_General_CP1_CI_AS
7SQLCHAR030""7FENAMESQL_Latin1_General_CP1_CI_AS
8SQLCHAR04""8FETYPESQL_Latin1_General_CP1_CI_AS
9SQLCHAR02""9FEDIRSSQL_Latin1_General_CP1_CI_AS
10SQLCHAR03""10CFCCSQL_Latin1_General_CP1_CI_AS
11SQLCHAR011""11FRADDLNULL
12SQLCHAR011""12TOADDLNULL
13SQLCHAR011""13FRADDRNULL
14SQLCHAR011""14TOADDRNULL
15SQLCHAR01""15FRIADDLSQL_Latin1_General_CP1_CI_AS
16SQLCHAR01""16TOIADDLSQL_Latin1_General_CP1_CI_AS
17SQLCHAR01""17FRIADDRSQL_Latin1_General_CP1_CI_AS
18SQLCHAR01""18TOIADDRSQL_Latin1_General_CP1_CI_AS
19SQLCHAR05""19ZIPLNULL
20SQLCHAR05""20ZIPRNULL
21SQLCHAR05""21AIANHHFPLNULL
22SQLCHAR05""22AIANHHFPRNULL
23SQLCHAR01""23AIHHTLILSQL_Latin1_General_CP1_CI_AS
24SQLCHAR01""24AIHHTLIRSQL_Latin1_General_CP1_CI_AS
25SQLCHAR01""25CENSUS1SQL_Latin1_General_CP1_CI_AS
26SQLCHAR01""26CENSUS2SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR02""27STATELNULL
28SQLCHAR02""28STATERNULL
29SQLCHAR03""29COUNTYLNULL
30SQLCHAR03""30COUNTYRNULL
31SQLCHAR05""31COUSUBLNULL
32SQLCHAR05""32COUSUBRNULL
33SQLCHAR05""33SUBMCDLNULL
34SQLCHAR05""34SUBMCDRNULL
35SQLCHAR05""35PLACELNULL
36SQLCHAR05""36PLACERNULL
37SQLCHAR06""37TRACTLNULL
38SQLCHAR06""38TRACTRNULL
39SQLCHAR04""39BLOCKLNULL
40SQLCHAR04""40BLOCKRNULL
41SQLCHAR010""41FRLONGNULL
42SQLCHAR0 9""42FRLATNULL
43SQLCHAR010""43TOLONGNULL
44SQLCHAR09"\r\n"44TOLATNULL
The error message indicates an I/O error, are there any network problems, can
SQL Server see the file, does SQL Server have permissions to access the file?
http://sqlservercode.blogspot.com/
"Corey Bunch" wrote:

> I'm attempting to run bcp to copy some files into a database and
> receiving a format file error (below). I'm using someone else's format
> file that I know already works for them, and I don't know what the
> issue is. Below is the error, my bcp statement and format file....can
> anyone tell what the problem is? (Note - some of the Collation
> values are wrapped b/c of my newsreader - in the format file they are
> all on the appropriate line and tab delimited). Thanks.
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
> documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
> documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
> "c:\documents and settings\user\my
> documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
> 8.0
> 44
> 1SQLCHAR01""1RTSQL_Latin1_General_CP1_CI_AS
> 2SQLCHAR04""2VERSIONNULL
> 3SQLCHAR010""3TLIDNULL
> 4SQLCHAR01""4SIDE1NULL
> 5SQLCHAR01""5SOURCESQL_Latin1_General_CP1_CI_AS
> 6SQLCHAR02""6FEDIRPSQL_Latin1_General_CP1_CI_AS
> 7SQLCHAR030""7FENAMESQL_Latin1_General_CP1_CI_AS
> 8SQLCHAR04""8FETYPESQL_Latin1_General_CP1_CI_AS
> 9SQLCHAR02""9FEDIRSSQL_Latin1_General_CP1_CI_AS
> 10SQLCHAR03""10CFCCSQL_Latin1_General_CP1_CI_AS
> 11SQLCHAR011""11FRADDLNULL
> 12SQLCHAR011""12TOADDLNULL
> 13SQLCHAR011""13FRADDRNULL
> 14SQLCHAR011""14TOADDRNULL
> 15SQLCHAR01""15FRIADDLSQL_Latin1_General_CP1_CI_AS
> 16SQLCHAR01""16TOIADDLSQL_Latin1_General_CP1_CI_AS
> 17SQLCHAR01""17FRIADDRSQL_Latin1_General_CP1_CI_AS
> 18SQLCHAR01""18TOIADDRSQL_Latin1_General_CP1_CI_AS
> 19SQLCHAR05""19ZIPLNULL
> 20SQLCHAR05""20ZIPRNULL
> 21SQLCHAR05""21AIANHHFPLNULL
> 22SQLCHAR05""22AIANHHFPRNULL
> 23SQLCHAR01""23AIHHTLILSQL_Latin1_General_CP1_CI_AS
> 24SQLCHAR01""24AIHHTLIRSQL_Latin1_General_CP1_CI_AS
> 25SQLCHAR01""25CENSUS1SQL_Latin1_General_CP1_CI_AS
> 26SQLCHAR01""26CENSUS2SQL_Latin1_General_CP1_CI_AS
> 27 SQLCHAR02""27STATELNULL
> 28SQLCHAR02""28STATERNULL
> 29SQLCHAR03""29COUNTYLNULL
> 30SQLCHAR03""30COUNTYRNULL
> 31SQLCHAR05""31COUSUBLNULL
> 32SQLCHAR05""32COUSUBRNULL
> 33SQLCHAR05""33SUBMCDLNULL
> 34SQLCHAR05""34SUBMCDRNULL
> 35SQLCHAR05""35PLACELNULL
> 36SQLCHAR05""36PLACERNULL
> 37SQLCHAR06""37TRACTLNULL
> 38SQLCHAR06""38TRACTRNULL
> 39SQLCHAR04""39BLOCKLNULL
> 40SQLCHAR04""40BLOCKRNULL
> 41SQLCHAR010""41FRLONGNULL
> 42SQLCHAR0 9""42FRLATNULL
> 43SQLCHAR010""43TOLONGNULL
> 44SQLCHAR09"\r\n"44TOLATNULL
>

BCP problem - help?

I'm attempting to run bcp to copy some files into a database and
receiving a format file error (below). I'm using someone else's format
file that I know already works for them, and I don't know what the
issue is. Below is the error, my bcp statement and format file....can
anyone tell what the problem is? (Note - some of the Collation
values are wrapped b/c of my newsreader - in the format file they are
all on the appropriate line and tab delimited). Thanks.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
"c:\documents and settings\user\my
documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
8.0
44
1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 4 "" 2 VERSION NULL
3 SQLCHAR 0 10 "" 3 TLID NULL
4 SQLCHAR 0 1 "" 4 SIDE1 NULL
5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 11 "" 11 FRADDL NULL
12 SQLCHAR 0 11 "" 12 TOADDL NULL
13 SQLCHAR 0 11 "" 13 FRADDR NULL
14 SQLCHAR 0 11 "" 14 TOADDR NULL
15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 5 "" 19 ZIPL NULL
20 SQLCHAR 0 5 "" 20 ZIPR NULL
21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 2 "" 27 STATEL NULL
28 SQLCHAR 0 2 "" 28 STATER NULL
29 SQLCHAR 0 3 "" 29 COUNTYL NULL
30 SQLCHAR 0 3 "" 30 COUNTYR NULL
31 SQLCHAR 0 5 "" 31 COUSUBL NULL
32 SQLCHAR 0 5 "" 32 COUSUBR NULL
33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
35 SQLCHAR 0 5 "" 35 PLACEL NULL
36 SQLCHAR 0 5 "" 36 PLACER NULL
37 SQLCHAR 0 6 "" 37 TRACTL NULL
38 SQLCHAR 0 6 "" 38 TRACTR NULL
39 SQLCHAR 0 4 "" 39 BLOCKL NULL
40 SQLCHAR 0 4 "" 40 BLOCKR NULL
41 SQLCHAR 0 10 "" 41 FRLONG NULL
42 SQLCHAR 0 9 "" 42 FRLAT NULL
43 SQLCHAR 0 10 "" 43 TOLONG NULL
44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULLThe error message indicates an I/O error, are there any network problems, can
SQL Server see the file, does SQL Server have permissions to access the file?
http://sqlservercode.blogspot.com/
"Corey Bunch" wrote:
> I'm attempting to run bcp to copy some files into a database and
> receiving a format file error (below). I'm using someone else's format
> file that I know already works for them, and I don't know what the
> issue is. Below is the error, my bcp statement and format file....can
> anyone tell what the problem is? (Note - some of the Collation
> values are wrapped b/c of my newsreader - in the format file they are
> all on the appropriate line and tab delimited). Thanks.
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
> documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
> documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
> "c:\documents and settings\user\my
> documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
> 8.0
> 44
> 1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 4 "" 2 VERSION NULL
> 3 SQLCHAR 0 10 "" 3 TLID NULL
> 4 SQLCHAR 0 1 "" 4 SIDE1 NULL
> 5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 11 "" 11 FRADDL NULL
> 12 SQLCHAR 0 11 "" 12 TOADDL NULL
> 13 SQLCHAR 0 11 "" 13 FRADDR NULL
> 14 SQLCHAR 0 11 "" 14 TOADDR NULL
> 15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
> 16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
> 17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
> 18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
> 19 SQLCHAR 0 5 "" 19 ZIPL NULL
> 20 SQLCHAR 0 5 "" 20 ZIPR NULL
> 21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
> 22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
> 23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_AS
> 24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_AS
> 25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
> 26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
> 27 SQLCHAR 0 2 "" 27 STATEL NULL
> 28 SQLCHAR 0 2 "" 28 STATER NULL
> 29 SQLCHAR 0 3 "" 29 COUNTYL NULL
> 30 SQLCHAR 0 3 "" 30 COUNTYR NULL
> 31 SQLCHAR 0 5 "" 31 COUSUBL NULL
> 32 SQLCHAR 0 5 "" 32 COUSUBR NULL
> 33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
> 34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
> 35 SQLCHAR 0 5 "" 35 PLACEL NULL
> 36 SQLCHAR 0 5 "" 36 PLACER NULL
> 37 SQLCHAR 0 6 "" 37 TRACTL NULL
> 38 SQLCHAR 0 6 "" 38 TRACTR NULL
> 39 SQLCHAR 0 4 "" 39 BLOCKL NULL
> 40 SQLCHAR 0 4 "" 40 BLOCKR NULL
> 41 SQLCHAR 0 10 "" 41 FRLONG NULL
> 42 SQLCHAR 0 9 "" 42 FRLAT NULL
> 43 SQLCHAR 0 10 "" 43 TOLONG NULL
> 44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULL
>

BCP problem - help?

I'm attempting to run bcp to copy some files into a database and
receiving a format file error (below). I'm using someone else's format
file that I know already works for them, and I don't know what the
issue is. Below is the error, my bcp statement and format file....can
anyone tell what the problem is? (Note - some of the Collation
values are wrapped b/c of my newsreader - in the format file they are
all on the appropriate line and tab delimited). Thanks.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading B
CP
format file
bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
"c:\documents and settings\user\my
documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
8.0
44
1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 4 "" 2 VERSION NULL
3 SQLCHAR 0 10 "" 3 TLID NULL
4 SQLCHAR 0 1 "" 4 SIDE1 NULL
5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 11 "" 11 FRADDL NULL
12 SQLCHAR 0 11 "" 12 TOADDL NULL
13 SQLCHAR 0 11 "" 13 FRADDR NULL
14 SQLCHAR 0 11 "" 14 TOADDR NULL
15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 5 "" 19 ZIPL NULL
20 SQLCHAR 0 5 "" 20 ZIPR NULL
21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_A
S
24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_A
S
25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 2 "" 27 STATEL NULL
28 SQLCHAR 0 2 "" 28 STATER NULL
29 SQLCHAR 0 3 "" 29 COUNTYL NULL
30 SQLCHAR 0 3 "" 30 COUNTYR NULL
31 SQLCHAR 0 5 "" 31 COUSUBL NULL
32 SQLCHAR 0 5 "" 32 COUSUBR NULL
33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
35 SQLCHAR 0 5 "" 35 PLACEL NULL
36 SQLCHAR 0 5 "" 36 PLACER NULL
37 SQLCHAR 0 6 "" 37 TRACTL NULL
38 SQLCHAR 0 6 "" 38 TRACTR NULL
39 SQLCHAR 0 4 "" 39 BLOCKL NULL
40 SQLCHAR 0 4 "" 40 BLOCKR NULL
41 SQLCHAR 0 10 "" 41 FRLONG NULL
42 SQLCHAR 0 9 "" 42 FRLAT NULL
43 SQLCHAR 0 10 "" 43 TOLONG NULL
44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULLThe error message indicates an I/O error, are there any network problems, ca
n
SQL Server see the file, does SQL Server have permissions to access the file
?
http://sqlservercode.blogspot.com/
"Corey Bunch" wrote:

> I'm attempting to run bcp to copy some files into a database and
> receiving a format file error (below). I'm using someone else's format
> file that I know already works for them, and I don't know what the
> issue is. Below is the error, my bcp statement and format file....can
> anyone tell what the problem is? (Note - some of the Collation
> values are wrapped b/c of my newsreader - in the format file they are
> all on the appropriate line and tab delimited). Thanks.
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading
BCP
> format file
>
> bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
> documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
> documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
> "c:\documents and settings\user\my
> documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
> 8.0
> 44
> 1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 4 "" 2 VERSION NULL
> 3 SQLCHAR 0 10 "" 3 TLID NULL
> 4 SQLCHAR 0 1 "" 4 SIDE1 NULL
> 5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 11 "" 11 FRADDL NULL
> 12 SQLCHAR 0 11 "" 12 TOADDL NULL
> 13 SQLCHAR 0 11 "" 13 FRADDR NULL
> 14 SQLCHAR 0 11 "" 14 TOADDR NULL
> 15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
> 16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
> 17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
> 18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
> 19 SQLCHAR 0 5 "" 19 ZIPL NULL
> 20 SQLCHAR 0 5 "" 20 ZIPR NULL
> 21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
> 22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
> 23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_A
S
> 24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_A
S
> 25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
> 26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
> 27 SQLCHAR 0 2 "" 27 STATEL NULL
> 28 SQLCHAR 0 2 "" 28 STATER NULL
> 29 SQLCHAR 0 3 "" 29 COUNTYL NULL
> 30 SQLCHAR 0 3 "" 30 COUNTYR NULL
> 31 SQLCHAR 0 5 "" 31 COUSUBL NULL
> 32 SQLCHAR 0 5 "" 32 COUSUBR NULL
> 33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
> 34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
> 35 SQLCHAR 0 5 "" 35 PLACEL NULL
> 36 SQLCHAR 0 5 "" 36 PLACER NULL
> 37 SQLCHAR 0 6 "" 37 TRACTL NULL
> 38 SQLCHAR 0 6 "" 38 TRACTR NULL
> 39 SQLCHAR 0 4 "" 39 BLOCKL NULL
> 40 SQLCHAR 0 4 "" 40 BLOCKR NULL
> 41 SQLCHAR 0 10 "" 41 FRLONG NULL
> 42 SQLCHAR 0 9 "" 42 FRLAT NULL
> 43 SQLCHAR 0 10 "" 43 TOLONG NULL
> 44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULL
>sql

BCP Issue w/Right Truncation

Hello All,

I am attempting a bulk load of fixed position flat file data via bcp
and I have noticed that I get a Right Truncation error when trying to
load a row where the last column value is NULL.

For example:

Flat file row:
0000016M

FMT file:
7.0
3
1 SQLCHAR 0 7 "" 1 RECORD_KEY
2 SQLCHAR 0 1 "" 2 SEX
3 SQLCHAR 0 1 "\r\n" 3 HEIGHT

In this row, the height info is null and I get a right truncation
error. The row below, with height info goes in fine:

Flat file row:
0000016M510

Let me know what I am doing wrong!

Thanks in advancehharry (paulquigley@.nyc.com) writes:
> I am attempting a bulk load of fixed position flat file data via bcp
> and I have noticed that I get a Right Truncation error when trying to
> load a row where the last column value is NULL.
> For example:
> Flat file row:
> 0000016M
> FMT file:
> 7.0
> 3
> 1 SQLCHAR 0 7 "" 1 RECORD_KEY
> 2 SQLCHAR 0 1 "" 2 SEX
> 3 SQLCHAR 0 1 "\r\n" 3 HEIGHT
> In this row, the height info is null and I get a right truncation
> error. The row below, with height info goes in fine:
> Flat file row:
> 0000016M510

It would have help if you had posted a CREATE TABLE statement for your
table, and also provided a sample file where the is a row that fails.

But maybe you should try this file instead:

7.0
3
1 SQLCHAR 0 7 "" 1 RECORD_KEY
2 SQLCHAR 0 1 "" 2 SEX
3 SQLCHAR 0 3 "" 3 HEIGHT
4 SQLCHAR 0 0 "\n" 0

(I believe that \r\n or \n are the same here.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP IN column limit

I am encountering a limit when attempting to import/load/read a file using the BCP functions in SQL Server 2000. The import fails when I have around 200 columns. Is there a similar limit in SQL Server 2005? Stated differently, what is the maximum number of columns I can import with bcp? Are there other maximums I should be investigating? My incoming record length is about 2K.

Hi,

I'm having the same problem, but the limit seems to be about 100-120 columns in BCP from SQL Server 2000. Did you ever get a resolution for your issue? I'd appreciate if you'd share any findings.

Cheers

RichardS71

|||

No success so far. The project has been benched for a while. My current suspect is there is a buffer limit I'm encountering somewhere. but haven't heard or read about one yet. Are you using a straight bcp (bcp with format file), or using the C entry points? We're using the C method right now, and would like to test a straight bcp and see if that eliminates the problem.

Do you have any other ideas?

|||

I'm doing a straight BCP without a format file (using the -c switch to denote all the fields are characters) although I've tried it with a format file too and I'm getting the same problem. For a full description of prob see post with title BCP fails with 134 columns .

I'm also getting the feeling that it's an internal buffer issue. I passed the problem onto a collegue and he did his own example of bcp with 134 fields, and his example worked. His table name and column names were shorter. Think I may be getting closer to the answer. I'll post here if I do.

Regards

RichardS

|||

Ok, I've cracked my problem at least.

Whilst SQL Server allows you to create tables with column names that begin with a number, BCP version 8.00.XXX won't let you BCP to the table. You wouldn't believe how long and hard I've been trying to crack this one.

I hope this helps. BTW I've scoured the net and I've not found anything todo with a limitation on number of columns. My inclimation would be to look in another direction, like what you've called your columns , how long the identifiers are, do they start with numbers...that sort of thing.

Tell me how you get on....and good luck.

Regards

RichardS

BCP IN column limit

I am encountering a limit when attempting to import/load/read a file using the BCP functions in SQL Server 2000. The import fails when I have around 200 columns. Is there a similar limit in SQL Server 2005? Stated differently, what is the maximum number of columns I can import with bcp? Are there other maximums I should be investigating? My incoming record length is about 2K.

Hi,

I'm having the same problem, but the limit seems to be about 100-120 columns in BCP from SQL Server 2000. Did you ever get a resolution for your issue? I'd appreciate if you'd share any findings.

Cheers

RichardS71

|||

No success so far. The project has been benched for a while. My current suspect is there is a buffer limit I'm encountering somewhere. but haven't heard or read about one yet. Are you using a straight bcp (bcp with format file), or using the C entry points? We're using the C method right now, and would like to test a straight bcp and see if that eliminates the problem.

Do you have any other ideas?

|||

I'm doing a straight BCP without a format file (using the -c switch to denote all the fields are characters) although I've tried it with a format file too and I'm getting the same problem. For a full description of prob see post with title BCP fails with 134 columns .

I'm also getting the feeling that it's an internal buffer issue. I passed the problem onto a collegue and he did his own example of bcp with 134 fields, and his example worked. His table name and column names were shorter. Think I may be getting closer to the answer. I'll post here if I do.

Regards

RichardS

|||

Ok, I've cracked my problem at least.

Whilst SQL Server allows you to create tables with column names that begin with a number, BCP version 8.00.XXX won't let you BCP to the table. You wouldn't believe how long and hard I've been trying to crack this one.

I hope this helps. BTW I've scoured the net and I've not found anything todo with a limitation on number of columns. My inclimation would be to look in another direction, like what you've called your columns , how long the identifiers are, do they start with numbers...that sort of thing.

Tell me how you get on....and good luck.

Regards

RichardS

bcp hanging in SQL2000

I am attempting a very simple bcp out to a text file in SQL 2000 for the first time. I've tried various syntax but the command consistently hangs no matter which i use. Nothing is being directed to the output log.........

"bcp rfsspd..bcp_product_view out product.out -T -c -b500 -r>>test.log"

I have no problems with this command in SQL 6.5, could it be initial setup or config of 2000 or something really daft??

Any help greatly appreciatedMake sure your SQL server can find bcp.exe

C:\Program Files\Microsoft SQL Server\80\Tools\Binn\

Try using this command, see if it makes a difference

DECLARE @.command VARCHAR(255)

SELECT @.command = 'bcp dbName.dbo.tableName in c:\yourFolderName\....\yourTextFile.txt -n -UyourUserName -PyourPassword -fc:\yourFolderName\......\yourFormatFile.fmt -ec:\yourFolderName\.....\yourErrorFile.err'

EXEC spCmdShell @.command

This uses a format file - which you'll have to creat and replace the in with out

bcp format file annoyance

Hi All
I'm attempting to import a text file into sql server using bcp via an
appropriate format file. I keep getting the 'Incorrect host-column
number found in BCP format-file' error. I've managed to pare my large
text file down and still generate the same error. My input file is
comma-delimited and my text is qualified by double quotes:
"A/R #","Ordered","Date Billed","Diagnosis","Detail Comment"
"1","12/14/2004","03/15/2005","3219","",
"2","12/10/2004","04/19/2005","3312","test...",
"3","12/12/2004","09/29/2005","3300","",
Note: Every record is terminated by a new line character. A comma also
shows up at the end of every record starting with the second row. Most
fields in the "Detail Comment" column are blank ("").
I generated my format file by importing the txt file using DTS, then
running bcp format, etc. The format file follows (I replaced the
collation with "")
8.0
5
1 SQLCHAR 0 8000 "," 1 A/R # ""
2 SQLCHAR 0 8000 "," 2 Ordered ""
3 SQLCHAR 0 8000 "," 3 Date Billed ""
4 SQLCHAR 0 8000 "," 4 Diagnosis ""
5 SQLCHAR 0 8000 "\n," 5 Detail Comment ""
I think my problems might stem from the trailing comma, which I tried
to allow for in the record terminator. I've also tried "\r\n," as the
record terminator.
Any suggestions are greatly appreciated.
chloraphil@.gmail.com wrote:
> Hi All
> I'm attempting to import a text file into sql server using bcp via an
> appropriate format file. I keep getting the 'Incorrect host-column
> number found in BCP format-file' error. I've managed to pare my large
> text file down and still generate the same error. My input file is
> comma-delimited and my text is qualified by double quotes:
> "A/R #","Ordered","Date Billed","Diagnosis","Detail Comment"
> "1","12/14/2004","03/15/2005","3219","",
> "2","12/10/2004","04/19/2005","3312","test...",
> "3","12/12/2004","09/29/2005","3300","",
> Note: Every record is terminated by a new line character. A comma
> also shows up at the end of every record starting with the second
> row. Most fields in the "Detail Comment" column are blank ("").
> I generated my format file by importing the txt file using DTS, then
> running bcp format, etc. The format file follows (I replaced the
> collation with "")
> 8.0
> 5
> 1 SQLCHAR 0 8000 "," 1 A/R # ""
> 2 SQLCHAR 0 8000 "," 2 Ordered ""
> 3 SQLCHAR 0 8000 "," 3 Date Billed ""
> 4 SQLCHAR 0 8000 "," 4 Diagnosis ""
> 5 SQLCHAR 0 8000 "\n," 5 Detail Comment ""
> I think my problems might stem from the trailing comma, which I tried
> to allow for in the record terminator. I've also tried "\r\n," as the
> record terminator.
> Any suggestions are greatly appreciated.
Did you actually try to put the trailing comma *before* the newline in the
format file? Another issue might be column names with white space etc. in
them.
Kind regards
robert

BCP and Unicode (UTF-16LE)

I am attempting to load a UTF-16LE file using the BCP or Bulk Insert into a SQL Server 2000 db and have had limited success. I have been able to do it using the GUI DTS tools but would prefer to use the BULK INSERT or BCP.
Has anyone done this before?Pardon my ignorance, but what is UTF-16LE ?:confused:|||I should have been more explicit Little Endian...|||Never done this before, but if you can find out the code page, you can use the -C (capital C) switch for bcp to specify it. Otherwise, if you need to do this column by column, you will be reading up on format files. Hope this helps.|||I appreciate the response...

I have created a format file for the import that species SQLNCHAR as the datatype. The column width specifications are correct as well as the row terminators and col. delimiters. The prefix length is set to 0 since the file is being generated by an outside system.

I have tried the -C option for the UTF-16LE codepage but it seems to expect a number like 932 for SHIFT-JIS.