[dba-SQLServer] BCP

>You could also look at bcp to import your files, it should be faster.

These files come to me in a zip file (21 of them), unzipped they are each
exactly 9,706,055 bytes (just under 10 gigabytes).  Each contains exactly 3
million records.  Each record contains some 400+ fields, fixed width but
comma delimited.  The FIRST file has the header containing the field names
etc, but the remaining files do not.

Because of the size of the files, they are a pain to do anything with
programmatically (VBA etc).

BCP is a command line process correct?  I would love to do anything to speed
this up but basically the import wizard, while cumbersome to use over and
over, make it drop dead simple to get these imported.  IIRC BCP (which I
have never used) requires a format file?  Can I get SQL Server (EM?) to
create the format file for the table given the existing table (which I
have).  I sure don't want to 

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Michael
Maddison
Sent: Wednesday, August 09, 2006 9:33 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SQL Server hanging

John,

Yes SQL will stop and 'think' in situations like yours  :-) Stopping SQL
when it is busy is almost always a bad idea.
It will attempt to recover, roll back etc when you restart it again as you
discovered.

IIRC you can configure DTS to log any errors.
You can also specify that each package operates within a transaction.
1 fails all fails and rolls back, which is probably what was happening when
SQL stopped responding.
It takes a while to roll back a large number of transaction.  I have learnt
to give it the benefit of the doubt and 99/100 it will eventually roll back
and then display whatever the problem was with the package.  

Making the data chunks smaller is also a good idea.

You could also look at bcp to import your files, it should be faster.

cheers

Michael M