bulk insert failure

Question

Post reply

bulk insert failure

Green Armchair

Hall of Fame

Points: 3561
More actions
March 30, 2004 at 3:16 am

#160789

Hi
I've bcp'd a table out using:
bcp "select * from mis.dbo.vcro_ps_amount order by ID_PPLA_01,NUM_PSCH_01,PYMT_NUM_PSAM_01" queryout "N:\FTPSites\FTPRoot\unload\vcro_ps_amount.out" -T -S. -N
in order to load it back in again having changed some datatypes (the table's 40G so using alter table blew the transaction logs). I used the -N flag to speed up performance.
bulk insert using (after dropped and recreated the table):
bulk insert mis.dbo.vcro_ps_amount from 'N:\FTPSites\FTPRoot\unload\vcro_ps_amount.out'
with
( batchsize=50000,
datafiletype='widenative',
order (ID_PPLA_01,NUM_PSCH_01,PYMT_NUM_PSAM_01)
)
is failing with:
Bulk Insert fails. Column is too long in the data file for row 1, column 7. Make sure the field terminator and row terminator are specified correctly. "bcp in" also fails with similar errors.
I've tried various datafiletype settings and terminators but with no luck. The table does have some computed columns (the last 2 columns). I understood that using native datatypes, I don't need to specify terminators.
Any help would be appreciated.
Thanks
Andy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Green Armchair Hall of Fame Points: 3561 More actions · Answer 1

Hi

In another environment, I bcp'd out using -n (rather than -N) and the bulk insert back in worked fine.

Would still like to know how to get the data back in from data bcp'd out using -N.

Thanks

Andy

PhilPacha Hall of Fame Points: 3645 More actions · Answer 2

The -N option copies character data as Unicode. This is probably overflowing a non-Unicode character field during import. It would explain why using -n worked, since it uses the native datatypes, without conversion. You will probably have to use a BCP Format File to specify the character data re-conversion.

Green Armchair Hall of Fame Points: 3561 More actions · Answer 3

Hi

Thanks for the reply. I read BOL a little more carefully and realised that -N is quite different to -n. -n is what I should have used. Fortunately, I have been able to reload the data from another source. I did try bcp format files, but gave up eventually, having pulled out the remains of my hair!