bcp issue

Question

bcp issue

halifaxdal

SSCoach

Points: 19741
More actions
September 2, 2015 at 1:29 pm

#302874

I am starting the practice of mega data today, here is what I am doing now:
I have data generated from SSMS using query to file: the query option is set to "comma delimited", so I have data file that comes with comma as delimiter. As the data file is huge 6G(39977139 rows), to start the practice, I also created a test file that contains only the first 100 rows. I name it 20150902FullTest.rpt
I created a DB named Test on my local SQL server, and created the table Test that is to hold all the data bcp imported from the file. Here is what I did:
1. First of all I created a test database called: Test
2. created the table with the columns from the query, for the convenience, I have the script below in the attachment target.txt, please change .txt to .sql, this creates the target table Test
3. created the format file using bcp, here is the script:
bcp Test.dbo.Test format nul -c -t, -f C:\RXie\SQL\Test.fmt –T
please change the Test.txt to Test.fmt
The data file is also attached here 20150902FullTest.txt, please change the .txt to .rpt
With the format file and the data file, I use the following bcp command:
bcp Test.dbo.Test in C:\Rxie\SQL\20150902FullTest.rpt -f C:\Rxie\SQL\Test.fmt -T
The command failed, I've attached the error message here.

Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
....
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
BCP copy in failed
I have been scratching my head for the whole day and still no clue, can anyone help? big thanks.

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

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

halifaxdal SSCoach Points: 19741 More actions · Answer 1

I corrected an error in the format file, please use the new one attached here (change the .txt to fmt please), error becomes:

SQLState = 07006, NativeError = 0

Error = [Microsoft][SQL Native Client]Restricted data type attribute violation

Thanks.

halifaxdal SSCoach Points: 19741 More actions · Answer 2

Please discard this post, I will report to Steve to remove it completely.

I am to start everything again.

I am deeply sorry to this and want to apologize to everyone who spent time on it, hopefully not too many.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Your problem was that the table didn't match the file.

I suppose that that's what you found out (or maybe something we couldn't see).

I suggest you to read the following article for a nice introduction on bcp/bulk insert and format files.

http://www.sqlservercentral.com/articles/BCP+(Bulk+Copy+Program)/105867/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

halifaxdal SSCoach Points: 19741 More actions · Answer 4

Luis Cazares (9/2/2015)
Your problem was that the table didn't match the file.
I suppose that that's what you found out (or maybe something we couldn't see).
I suggest you to read the following article for a nice introduction on bcp/bulk insert and format files.
http://www.sqlservercentral.com/articles/BCP+(Bulk+Copy+Program)/105867/

Exactly, I just found out there is a mismatch and that caused this hassle, there is still issue in the bcp after I corrected the mistake, I will start a new post soon. Thank you for your understanding and I am so embarrassed when realized I made a mistake.