BCP Transaction Does not roll back

Hi All,
I'm trying to import some data using the BCP command line utility.
I've set the maxerrors switch to 0. In case of an exception such as a
cast exception the import fails leaving the table state dirtied.
ie...with partial data imported.
From what I understand from a few other posts the transaction logs
only store the space alocated and not the actual data.
How does one ensure that the import is done in a transaction ?
Any suggestions/ideas will be great.
Regards,
Avinash
Can you post the command you are running
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Avinash" <avinashraj@.gmail.com> wrote in message
news:f22f61b0.0412050250.3d6ef381@.posting.google.c om...
> Hi All,
> I'm trying to import some data using the BCP command line utility.
> I've set the maxerrors switch to 0. In case of an exception such as a
> cast exception the import fails leaving the table state dirtied.
> ie...with partial data imported.
> From what I understand from a few other posts the transaction logs
> only store the space alocated and not the actual data.
> How does one ensure that the import is done in a transaction ?
> Any suggestions/ideas will be great.
> Regards,
> Avinash

bcp - server-side failure ignore?

Hi,
Is there any way to cause bcp to carry on processing the load file
ignoring any duplicates (i.e. true duplicates in my case)? It aborts
the load at the first violation. (Dropping the primary key constraint
is not an option).
It appears that the -m option does not apply to constraint checks or
any server side errors.
I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
joy either.
I know that I can load into a staging table and insert/select 'where
not exists'.
Are there other alternatives using bcp alone? If not, it seems like a
glaring omission.
Liam CaffreyYou could change the index to IGNORE_DUP_KEY - might work.
"liam.caffrey@.gmail.com" wrote:

> Hi,
> Is there any way to cause bcp to carry on processing the load file
> ignoring any duplicates (i.e. true duplicates in my case)? It aborts
> the load at the first violation. (Dropping the primary key constraint
> is not an option).
> It appears that the -m option does not apply to constraint checks or
> any server side errors.
> I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
> joy either.
> I know that I can load into a staging table and insert/select 'where
> not exists'.
> Are there other alternatives using bcp alone? If not, it seems like a
> glaring omission.
> Liam Caffrey
>

bcp - server-side failure ignore?

Hi,
Is there any way to cause bcp to carry on processing the load file
ignoring any duplicates (i.e. true duplicates in my case)? It aborts
the load at the first violation. (Dropping the primary key constraint
is not an option).
It appears that the -m option does not apply to constraint checks or
any server side errors.
I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
joy either.
I know that I can load into a staging table and insert/select 'where
not exists'.
Are there other alternatives using bcp alone? If not, it seems like a
glaring omission.
Liam Caffrey
You could change the index to IGNORE_DUP_KEY - might work.
"liam.caffrey@.gmail.com" wrote:

> Hi,
> Is there any way to cause bcp to carry on processing the load file
> ignoring any duplicates (i.e. true duplicates in my case)? It aborts
> the load at the first violation. (Dropping the primary key constraint
> is not an option).
> It appears that the -m option does not apply to constraint checks or
> any server side errors.
> I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
> joy either.
> I know that I can load into a staging table and insert/select 'where
> not exists'.
> Are there other alternatives using bcp alone? If not, it seems like a
> glaring omission.
> Liam Caffrey
>

bcp - server-side failure ignore?

Hi,
Is there any way to cause bcp to carry on processing the load file
ignoring any duplicates (i.e. true duplicates in my case)? It aborts
the load at the first violation. (Dropping the primary key constraint
is not an option).
It appears that the -m option does not apply to constraint checks or
any server side errors.
I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
joy either.
I know that I can load into a staging table and insert/select 'where
not exists'.
Are there other alternatives using bcp alone? If not, it seems like a
glaring omission.
Liam CaffreyYou could change the index to IGNORE_DUP_KEY - might work.
"liam.caffrey@.gmail.com" wrote:
> Hi,
> Is there any way to cause bcp to carry on processing the load file
> ignoring any duplicates (i.e. true duplicates in my case)? It aborts
> the load at the first violation. (Dropping the primary key constraint
> is not an option).
> It appears that the -m option does not apply to constraint checks or
> any server side errors.
> I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
> joy either.
> I know that I can load into a staging table and insert/select 'where
> not exists'.
> Are there other alternatives using bcp alone? If not, it seems like a
> glaring omission.
> Liam Caffrey
>