Incorrect host-column number found in BCP format-file

Hi guys, would appreciate if you can shed some light on this.

Sorry to be a pain, can you tell me what is wrong with the following:

for /F %%i in ('dir /b /on c:\bcp\pc*.txt') do bcp Inventory..pc in
%%i -fc:\bcp\bcp.fmt -T -S CHICKYy
where CHICKYy is the server

bcp.fmt

8.00.194
6
1 SQLCHAR 0 20 ", " 0 filler_1 ""
2 SQLCHAR 0 8 "\r\n" 1 computer_name ""
3 SQLCHAR 0 20 ", " 0 filler_2 ""
4 SQLCHAR 0 16 "\r\n" 2 ip_address ""
5 SQLCHAR 0 20 ", " 0 filler_3 ""
6 SQLCHAR 0 60 "\r\n" 3 operating_system ""

pc1.txt and other *.txt format is:

JW_193801,
192.168.1.1,
Windows XP,

when I run it I get:

C:\bcp>for /F %i in ('dir /b /on c:\bcp\pc*.txt') do bcp Inventory..pc in
%i -fc:\bcp\bcp.fmt -T -S CHICKYy

C:\bcp>bcp Inventory..pc in pc1.txt -fc:\bcp\bcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file

C:\bcp>bcp Inventory..pc in pc2.txt -fc:\bcp\bcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file

C:\bcp>bcp Inventory..pc in pc3.txt -fc:\bcp\bcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file

C:\bcp>bcp Inventory..pc in pc4.txt -fc:\bcp\bcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file

C:\bcp>bcp Inventory..pc in pc5.txt -fc:\bcp\bcp.fmt -T -S CHICKYy
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file

The sql table has 3 columns:

Sorry to be a pain.

--

---------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.comMichelle Hillard (mhillard@.craized.tv) writes:
> bcp.fmt
> 8.00.194
> 6
> 1 SQLCHAR 0 20 ", " 0 filler_1 ""
> 2 SQLCHAR 0 8 "\r\n" 1 computer_name ""
> 3 SQLCHAR 0 20 ", " 0 filler_2 ""
> 4 SQLCHAR 0 16 "\r\n" 2 ip_address ""
> 5 SQLCHAR 0 20 ", " 0 filler_3 ""
> 6 SQLCHAR 0 60 "\r\n" 3 operating_system ""
>...
> C:\bcp>for /F %i in ('dir /b /on c:\bcp\pc*.txt') do bcp Inventory..pc in
> %i -fc:\bcp\bcp.fmt -T -S CHICKYy
> C:\bcp>bcp Inventory..pc in pc1.txt -fc:\bcp\bcp.fmt -T -S CHICKYy
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
> found in BCP format-file

BCP's is not famous for its self-explanatory messages.

My guess goes to the version number. It should say 8.0, not 8.00.194.

(And if you are running 8.00.194 somewhere, you should download and
install SP3 for SQL Server to get a couple of important bug and
security fixes.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

IN SQL SERVER BCP

Hi,

I have to insert a data from text file to Sql Server Using BULK INSERT ie BCP. Throgh qurey wise it execute fine.
But using stored procedure it does not work..here just i have to pass 1 parameter ie file name. The err is

" Could not bulk insert. File '@.BasicFile' does not exist." How can i solve the prolblem ?

Thanks and Regards,

ArulIt looks like you are trying to use a variable filename for your source file. As far as I know, you will need to use dynamic SQL to accomplish this.

SET @.SQL = "BULK INSERT myTable '"+@.PathFileName+"' WITH (FIELDTERMINATOR = ',') "
EXEC (@.SQL)

Terri