BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?
Hi Nick
"Nick" wrote:

> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John