Bulk Insert not importing all records

Hi,
I am using the bulk insert to import over 26,000,000 records about 55 bytes
long. The insert seems to only want to import 1,780,000 records. I'm not
getting any errors and this works fine on smaller files. I have increased my
commandtimout to 260000 so I don't think that is the problem. Any help would
be appreciated.
Thanks,
EllieI don't know what your problem is, but I would be inclined to try
running multiple passes, using FIRSTROW and LASTROW to load a million
at a time. There is no guarantee that the same problem, whatever it
is, will not show up anyway though.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 15:51:45 -0400, "Ellie" <nospam@.nospam.net> wrote:

>Hi,
>I am using the bulk insert to import over 26,000,000 records about 55 bytes
>long. The insert seems to only want to import 1,780,000 records. I'm not
>getting any errors and this works fine on smaller files. I have increased m
y
>commandtimout to 260000 so I don't think that is the problem. Any help woul
d
>be appreciated.
>Thanks,
>Ellie
>|||Ellie (nospam@.nospam.net) writes:
> I am using the bulk insert to import over 26,000,000 records about 55
> bytes long. The insert seems to only want to import 1,780,000 records.
> I'm not getting any errors and this works fine on smaller files. I have
> increased my commandtimout to 260000 so I don't think that is the
> problem. Any help would be appreciated.
In additions to Roy's suggestion, try the -e option. This option generates
an error file, this may reveal something.
Rather than running multiple passes with -F and -L, you can set the
batch size with -b.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Here is my bulk insert command. It works fine in query analyzer but not in
my VB6 application.
BULK INSERT dataconflict.dbo.tblTemp from 'c:\output\tt060302.txt'
WITH (BATCHSIZE=10000, FORMATFILE='c:\temp\Temp.fmt')
Where would I put the -e option?
Thank you for your help.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B81E3E4CCAYazorman@.127.0.0.1...
> Ellie (nospam@.nospam.net) writes:
> In additions to Roy's suggestion, try the -e option. This option generates
> an error file, this may reveal something.
> Rather than running multiple passes with -F and -L, you can set the
> batch size with -b.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ellie (nospam@.nospam.net) writes:
> Here is my bulk insert command. It works fine in query analyzer but not in
> my VB6 application.
Hmmm. It's not that the VB6 application times out? The default timeout
from client APIs is 30 secounds.

> BULK INSERT dataconflict.dbo.tblTemp from 'c:\output\tt060302.txt'
> WITH (BATCHSIZE=10000, FORMATFILE='c:\temp\Temp.fmt')
> Where would I put the -e option?
Sorry, I did not observer that you were using BULK INSERT. My advice was
for command-line BCP.
BULK INSERT does not seem to have any option corresponding to -e. Anyway, if
it works from Query Analyzer, I don't think there are any errors in the
file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Not sure what you mean by that. How would I change it? I made a change to
the commandtimeout to 0 so it is unlimited.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B8A74A8E5A5Yazorman@.127.0.0.1...
> Ellie (nospam@.nospam.net) writes:
> Hmmm. It's not that the VB6 application times out? The default timeout
> from client APIs is 30 secounds.
>
> Sorry, I did not observer that you were using BULK INSERT. My advice was
> for command-line BCP.
> BULK INSERT does not seem to have any option corresponding to -e. Anyway,
> if
> it works from Query Analyzer, I don't think there are any errors in the
> file.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ellie (nospam@.nospam.net) writes:
> Not sure what you mean by that. How would I change it? I made a change to
> the commandtimeout to 0 so it is unlimited.
If you already have set the commandtimeout to 0, I'm afraid that I have
no idea what is going on.
If you have the proper access on the server, you could set up a Profiler
trace, and include the events in the Errors and Exception group, to
see if this brings up something.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thanks for your help. I have managed to get all of the records imported by
changing the batchsize from 10000 to 100000. I don't know why that would
make a difference though.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B917BE14B6Yazorman@.127.0.0.1...
> Ellie (nospam@.nospam.net) writes:
> If you already have set the commandtimeout to 0, I'm afraid that I have
> no idea what is going on.
> If you have the proper access on the server, you could set up a Profiler
> trace, and include the events in the Errors and Exception group, to
> see if this brings up something.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ellie (nospam@.nospam.net) writes:
> Thanks for your help. I have managed to get all of the records imported by
> changing the batchsize from 10000 to 100000. I don't know why that would
> make a difference though.
Neither have I!
I'm glad to hear that you got it working.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx