Monday, January 24, 2011

Bulk Insert Stored Procedure not returning 0 row(s) affected (Error)

Lately,

A while ago, a friend of mine sent me a CSV file whose fields are separated by comma. I followed a link from http://www.sqlteam.com in creating a stored procedure via BULK INSERT. This complex stored procedure inserts rows from a csv file to a database table with a field defined as primary key (identity seed option set).

After changing the script from the website and executed the SP below:
1:  use clientdb  
2:  go  
3:  EXEC Customer_Import 'D:\Csharp progs\Orders\Client.csv', 2  

The message returned in the result pane was: 0 row(s) affected. Wow, how could this happend where in fact, the csv has 50 rows? I've changed other options to the SP but got no luck. The solution i ran into was to re-open the CSV file and save as another file. Ex. (from Client to Client1). So, when i executed the SP above using the other file, it performed perfectly.
 use clientdb  
 go  
 EXEC Customer_Import 'D:\Csharp progs\Orders\Client1.csv', 2  

So, I guess there must be a problem with the original file, like corrupt data or incorrect data format.

0 comments:

Post a Comment