bcp out and bulk insert

• Johnny B

  SSCrazy

  Points: 2965

  More actions

  June 28, 2006 at 1:13 pm

  #171497

  Hi I am trying to figure out if Im at risk losing data when I bcp OUT and then BULK insert between physical servers. My concern is the codepage and or collation.(is this the same thing? does the codepage determine the collation?

  Any way I understand the -N flag on bcp give the best performance. But when I bulk insert with the 'native' datafiletype I get errors indicative of a datafile type mismatch.

  For example this one workds ---

  EXEC master.dbo.xp_cmdshell "BCP db.dbo.table OUT C:\bcpf\table1.dat -e C:\bcpf\table1.err -S OHJOHN -T -m 10 -w"

  BULK INSERT staging.dbo.qb_account FROM 'C:\bcpf\table1.dat'

  WITH (

  DATAFILETYPE = 'widechar',

  --CODEPAGE = 'OEM',

  TABLOCK

  )

  now change the OUT and INSERT flags to native unicode (supposedly the fastest) it breaks

  EXEC master.dbo.xp_cmdshell "BCP db.dbo.table OUT C:\bcpf\table1.dat -e C:\bcpf\table1.err -S OHJOHN -T -m 10 -N"

  BULK INSERT staging.dbo.qb_account FROM 'C:\bcpf\table1.dat'

  WITH (

  DATAFILETYPE = 'native',

  --CODEPAGE = 'OEM',

  TABLOCK

  )

  now i try a codepage and it works....

  EXEC master.dbo.xp_cmdshell "BCP db.dbo.table OUT C:\bcpf\table1.dat -e C:\bcpf\table1.err -SOHJOHN -T -m 10 -c -C850"

  BULK INSERT staging.dbo.qb_account FROM 'C:\bcpf\table1.dat'

  WITH (

  CODEPAGE = '850',

  TABLOCK

  )

  How do I find the code page i should use between the two servers? If the codepages are different between the two servers can I use native datafile types?

  thanks if you can shine some light on this for me

  J

  SQL 2012 Standard VPS Windows 2012 Server Standard

• Johnny B

  SSCrazy

  Points: 2965

  More actions

  June 29, 2006 at 10:32 am

  #646786

  DATAFILETYPE = 'widenative'

  that was the problem.

  SQL 2012 Standard VPS Windows 2012 Server Standard

• Johnny B

  SSCrazy

  Points: 2965

  More actions

  June 29, 2006 at 10:33 am

  #646789

  DATAFILETYPE = 'widenative'

  that was the problem.

  SQL 2012 Standard VPS Windows 2012 Server Standard