I have a text file consisting of 7 fields that are all defined as character. The field, FIELD7, defined in the text file layout as char(6) is defined as INT in the SQL Server 2000 table. I have two rows in the text file. My first problem is when I run the Bulk Copy command, the value of FIELD7 turns out to be 808465200 but is supposed to be 30. The value is stored in the text file as 003000. The second problem, is that it only imports 1 row. Below is the format file I am using:
8.0
9
1 SQLCHAR 0 9 "" 1 Field1
2 SQLCHAR 0 17 "" 2 Field2
3 SQLCHAR 0 4 "" 4 Field4
4 SQLCHAR 0 35 "" 3 Field3
5 SQLCHAR 0 4 "" 5 Field5
6 SQLCHAR 0 2 "" 6 Field6
7 SQLINT 1 4 "\r\n" 7 Field7
8 SQLCHAR 0 0 "" 0 Field8
9 SQLINT 0 0 "\r\n" 0 Field9
The column collation column is left off for readability but its value is SQL_Latin1_General_CP1_CI_AS for all columns except Field7 and Field9 where it is "". I built the format file using the BCP command.
The last entries in the format file are not provided in the the text file. The terminator of \r\n is being used because I was getting an error in the error log that an unexpected EOF was encountered. I have to use the table and text file definition as they are.
Any help would be greatly appreciated.
8.0
9
1 SQLCHAR 0 9 "" 1 Field1
2 SQLCHAR 0 17 "" 2 Field2
3 SQLCHAR 0 4 "" 4 Field4
4 SQLCHAR 0 35 "" 3 Field3
5 SQLCHAR 0 4 "" 5 Field5
6 SQLCHAR 0 2 "" 6 Field6
7 SQLINT 1 4 "\r\n" 7 Field7
8 SQLCHAR 0 0 "" 0 Field8
9 SQLINT 0 0 "\r\n" 0 Field9
The column collation column is left off for readability but its value is SQL_Latin1_General_CP1_CI_AS for all columns except Field7 and Field9 where it is "". I built the format file using the BCP command.
The last entries in the format file are not provided in the the text file. The terminator of \r\n is being used because I was getting an error in the error log that an unexpected EOF was encountered. I have to use the table and text file definition as they are.
Any help would be greatly appreciated.