need to understand the bulk insert

• JohnG69

  Hall of Fame

  Points: 3114

  More actions

  July 22, 2015 at 2:32 pm

  #319133

  Hi all,

  I'm testing a procedure on a combination of different Operating Systems with different versions of SQL express with the same data on all environments.

  Win7 + SQL2014 --> bcp queryout to a file and then bulk insert the same file --> works great!

  Win7 + SQL2008 --> bcp queryout to a file and then bulk insert the same file --> works great!

  XP + SQL2008 --> bcp queryout to a file and then bulk insert the same file --> Error" Bulk Load Data Conversion error (type mismatch or invalid character for the specified code page)

  I don't specify any code page!!!

  XP and win7 have regional settings set to English (united states)

  in both cases SQL 2008 collation is set to SQL_Latin1_General_CP1_CI_AS...

  what does Bulk insert do differently from one system to the other?

  I'm starting to think that it's the OS... can it be?

  or am I missing something...

  thanks in advance for your help .

  Thanks

  JG

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  July 22, 2015 at 3:34 pm

  #1814239

  jghali (7/22/2015)

  ---

  Hi all,

  I'm testing a procedure on a combination of different Operating Systems with different versions of SQL express with the same data on all environments.

  Win7 + SQL2014 --> bcp queryout to a file and then bulk insert the same file --> works great!

  Win7 + SQL2008 --> bcp queryout to a file and then bulk insert the same file --> works great!

  XP + SQL2008 --> bcp queryout to a file and then bulk insert the same file --> Error" Bulk Load Data Conversion error (type mismatch or invalid character for the specified code page)

  I don't specify any code page!!!

  XP and win7 have regional settings set to English (united states)

  in both cases SQL 2008 collation is set to SQL_Latin1_General_CP1_CI_AS...

  what does Bulk insert do differently from one system to the other?

  I'm starting to think that it's the OS... can it be?

  or am I missing something...

  thanks in advance for your help .

  Thanks

  JG

  Lookup CODEPAGE = "RAW" for BULK INSERT.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• JohnG69

  Hall of Fame

  Points: 3114

  More actions

  July 23, 2015 at 6:25 am

  #1814345

  Hi,

  I looked up CODEPAGE=RAW for Bulk Insert and Tried it too.

  still unsuccessful.

  I've been searching to understand the behavior of bulk insert under different operating systems and can't find anything on the web.

  The bcp and bulk insert work fine on SQL 2008 under windows 7 but gives an error under SQL 2008 under XP...

  I checked the versions of BCP and they're the same, since I haven't found a way to find the version of Bulk insert, I suppose the version goes with SQL2008 so should match.

  maybe the version of the copy command? I use the copy /b command to merge the title row with the data (as I found over the web)...

  I continue to try to understand and search for answers...

  Any idea is welcome.

  Thanks

  JG