| Author |
Topic |
|
sathishkumar
Starting Member
21 Posts |
 Posted - 2001-12-21 : 09:40:31
|
| SQLServer doing bulk insert from a remote client. in half way the communication between SQLServer and client broken. what will happen to the records inserted so for. with regardssathish |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-21 : 10:08:49
|
| If you have a batch size specified then it will commit every batch and you will be left with the last committed batch.If you don't specify a batch then no commit will take place until the end and you will get all or nothing.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
sathishkumar
Starting Member
21 Posts |
Posted - 2001-12-24 : 00:42:47
|
| No, the bulk insert fails half the way it commits the records fetched from the file so far. if we don't want the inconsistent data we have to capture the error and rollback. After experimenting i found this.Thanx a lotsathish |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-24 : 01:22:52
|
| i seriously doubt that Satish. this is what BOL has to say about Batchsize and what nr says is true. Though i havent tried it, but i didnt find any thing anywhere to suggest that this isnt true. can u tell me which version of Sql Server u r using.BATCHSIZE = nSpecifies the number of rows in a batch. Each batch is copied to the server as one transaction. SQL Server commits or rolls back, in the case of failure, the transaction for every batch. By default, all data in the specified data file is one batch. -------------------------Graz's Baby is my Master:) |
|
|
|
sathishkumar
Starting Member
21 Posts |
Posted - 2001-12-24 : 09:21:39
|
| Hi , i am using SQL2000, but i am not mentioning Batchsize for bulkinsert.to avoid in consistent data,in case of failure i am capturing the error and rolling back to last commited position.with regardssathish |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-24 : 10:26:00
|
| Satish What surprises me is when u dont specify the Batchsize it takes the whole transaction as a batch. so as nr writes if any error comes the whole transaction is rollback .in your case am wondering to what last commit you have rolledback . -------------------------Graz's Baby is my Master:) |
|
|
|
sathishkumar
Starting Member
21 Posts |
Posted - 2001-12-25 : 04:56:50
|
| Hi, I just rolling back all the records inserted through this bulkinsert.because half the way the bulk inserting procedure failed. so the calling module will call this procedures with the same file of data. because of half the data existing the new bulk insert will fail due to primary key error to avoid that we are doing the rollback to undo the records inserted through this bulk insert.with warm regardssathish |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-25 : 07:38:18
|
Satish, your earlier posts suggested that bulkinsert was committing till it found errors and it was you who was explicitly rolling back. what i was trying to explain was that you dont have to explicitly rollback ,it automatically rollbacks to the last batch and as u havent given batch size . it rollbacks the whole bulkinsert transaction.quote:
Hi , i am using SQL2000, but i am not mentioning Batchsize for bulkinsert.to avoid in consistent data,in case of failure i am capturing the error and rolling back to last commited position.
quote:
No, the bulk insert fails half the way it commits the records fetched from the file so far. if we don't want the inconsistent data we have to capture the error and rollback. After experimenting i found this.
quote:
i am using SQL2000, but i am not mentioning Batchsize for bulkinsert.to avoid in consistent data,in case of failure i am capturing the error and rolling back to last commited position.
-------------------------Graz's Baby is my Master:)Edited by - Nazim on 12/26/2001 02:43:17 |
|
|
|
sathishkumar
Starting Member
21 Posts |
Posted - 2001-12-26 : 01:17:39
|
| hai,bulk insert is not rolling back automaticallly while it failing. we have to force that. initially i thought as like you and later while experimenting i found that it is not rolling back.with regardssathish |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-26 : 12:48:35
|
| Have you looked into the settings for SET XACT_ABORT or SET IMPLICIT_TRANSACTIONS? These might be affecting the results you're seeing. |
|
|
|
sathishkumar
Starting Member
21 Posts |
Posted - 2001-12-31 : 04:07:09
|
| ya, i have look in to that, and also how bulk insert working is a intersting aspect. in the data file if some records are not in the specified format bulk insert ignores them and able to insert other records. and also it gives which are records are not inserted and what is the problem in that.with regardssathish |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-31 : 07:27:35
|
| Hey! Satish stop that. y r u posting the same thing over and over again. if you are trying to gain attention then that's a pretty bad way of doing it.Edited by - Nazim on 12/31/2001 07:28:58 |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2001-12-31 : 09:09:55
|
quote:
Hey! Satish stop that. y r u posting the same thing over and over again. if you are trying to gain attention then that's a pretty bad way of doing it.
Duplicate posts deleted.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
sathishkumar
Starting Member
21 Posts |
Posted - 2002-01-01 : 00:01:04
|
| Hi, sorry man, it some what it get repeated, this is not my mistake,i don't have that much free time, no need for me to impress or attract others.Happy new year to every body come across this postwith regardssathish |
|
|
|
|
regarding bulk insert