Because it can handle the string overflow... it truncates it. It even tells you that's what's going on. Bad data types it doesn't necessarily know how to deal with it.

I still don't see why you need (or think you need) transactions at all.

Dump the data into a staging table that is capable of handling the data, even if it's not accurate. When possible, use the correct data type if you can. When you want to commit it, take it from the staging table and insert it into it's final table. That's typically how it's done.

Or... create a properly type specific datatable that mirrors your table... dump the data into that and then to commit it, dump the data into the final table.

What's happening is that your transaction is turning into a distributed transaction... it's getting passed along your connection to SQL server, so when it encounters an error, it sees that there is a transaction and does an implicit rollback... this takes everything out.

Again though, I'll re-iterate that there isn't a need for the transactions in the first place, especially for the kind of result you're aiming for.

-tg