Hi, I'm copying date from a  sql server 2000 table to a sql server 2008 R2 table using a Linked Server.

I need to be sure the remote table is correct before processing it.

I first delete all the remote table contents and after that, I insert into remote_table from local_table

What happens if the communication is interrupted during the insert?

I mean, Can I be sure all records were copied just checking if record count > 0 ?

Thanks

Diego

Hi, I'm copying date from a  sql server 2000 table to a sql server 2008 R2 table using a Linked Server.

I need to be sure the remote table is correct before processing it.

I first delete all the remote table contents and after that, I insert into remote_table from local_table

What happens if the communication is interrupted during the insert?

I mean, Can I be sure all records were copied just checking if record count > 0 ?

Thanks

Diego

Ideally if connection gets interrupted it will try to rollback the transaction and hence you should not find any records in the table.

So what you could do is to check if record count > 0 to see if it was success or not. In case you want to rerun, delete existing data when count > 0 before you do the rerun.

Can I be sure all records were copied

Hello Diego,

If you perform DML via a linked server a distributed transaction is started via MS DTC; so you can be sure: All or nothing, if it fails a rollback is started.

See Performing Distributed Transactions, you even can start explicit a distributed Transaction with BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)