Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk Insert batch error Expand / Collapse
Author
Message
Posted Sunday, July 5, 2009 10:29 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 3, 2011 4:58 PM
Points: 88, Visits: 191
Hello Team,

I am getting following error when I use bulk insert command.

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 477, column 1 (Examid).


Command used:
BULK INSERT FileImportDB..Exam FROM
'F:\70-431 Material\Practice Files\Chapter 10\ExamImportFile.txt' --WITH (TABLOCK )
with (FIRSTROW = 1
,DATAFILETYPE ='char'
,ROWS_PER_BATCH = 5
)

There are only 500 rows in the file. Amazingly one one row, where the error occurred, is not inserted. According to BOL the batch should be rolled back and later batches should not be executed.

BOL: "Each batch of rows is inserted as a separate transaction. If, for any reason, the bulk-import operation terminates before completion, only the current transaction is rolled back. For instance, if a data file has 1000 rows, and a batch size of 100 is used, Microsoft SQL Server logs the operation as 10 separate transactions; each transaction inserts 100 rows into the destination table. If the bulk-import operation terminates while loading row 750, only the previous 49 rows are removed as SQL Server rolls back the current transaction. The destination table still contains the first 700 rows.
"


-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
Post #747499
Posted Monday, July 6, 2009 7:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 3:44 AM
Points: 29, Visits: 319
Michael,

ROWS_PER_BATCH is not the same as BATCHSIZE (check out BOL), and is only used for optimizing, not for transactional purposes. That may explain the behaviour you experience.
Post #747705
Posted Monday, July 6, 2009 7:34 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 3, 2011 4:58 PM
Points: 88, Visits: 191
According to my understanding ROWS_PER_BATCH is same as BATCHSIZE.

The sentence in my previous post is from BOL only and when this describes 100 as batch size then it is same as rows per batch


-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
Post #747718
Posted Monday, July 6, 2009 7:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 3:44 AM
Points: 29, Visits: 319
BOL 2000 says:

Using ROWS_PER_BATCH
If the -b switch or BATCHSIZE clause is not used, the entire file is sent to SQL Server and the bulk copy operation is treated as a single transaction. In this case, the ROWS_PER_BATCH hint or ROWS_PER_BATCH clause can be used to give an estimate of the number of rows. SQL Server optimizes the load automatically, according to the batch size value, which may result in better performance.

BOL 2005 says

ROWS_PER_BATCH = rows_per_batch
Indicates the approximate number of rows of data in the data file.

By default, all the data in the data file is sent to the server as a single transaction, and the number of rows in the batch is unknown to the query optimizer. If you specify ROWS_PER_BATCH (with a value > 0) the server uses this value to optimize the bulk-import operation. The value specified for ROWS_PER_BATCH should approximately the same as the actual number of rows.

Post #747721
Posted Monday, July 6, 2009 9:49 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 3, 2011 4:58 PM
Points: 88, Visits: 191
This is what I don't understand:
"ROWS_PER_BATCH should approximately the same as the actual number of rows"

It means that all the rows would be in the same batch. And we are good if we give a hint of batch size rather than row size.

The word 'ROWS_PER_BATCH' gives an impression that these many rows will be sent to sql server per batch instead of sending all the rows at the same time. But the sentence contradicts this:
""ROWS_PER_BATCH should approximately the same as the actual number of rows"


-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
Post #747847
Posted Tuesday, July 7, 2009 1:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 3:44 AM
Points: 29, Visits: 319
It may look somewhat confusing. Check out the explanation in BOL 2005 on 'Managing Batches for Bulk Import'. ROWS_PER_BATCH is a performance parameter, not a parameter that manages the batch size. BATCHSIZE takes precedence when both are specified.
Post #748344
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse