need to understand the bulk insert

Question

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

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

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 · Answer 2

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