I have a DTS package (sql server 2000 at the moment, but could be migrated to 2005), that basically does the following:
- drop local tables / data
- connect to remote SQL server over the Internet
- transfer the new data over from that server
Now, the problem is simple: in the first step all the local data is deleted, but if consequently the connection to the remote server fails, the users are left with no data!!!
So how it SHOULD work is that the drop tables are ONLY executed IF the connection succeeds!
What's the easiest way to accomplish this?
Thanks for your suggestions!
the way i would do is
1. Connect to remote SQL server over the Internet
2. transfer the new data over from that server (as tmp tables)
3. once the data transfer is complete delete the original tables
4. rename the tmp tables.
Have you considered publish/subscribe replication?
Transactiosns and temp tables my friend........persoanlly I think I would just go with transactions, but temp tables might be handy
I'll try the transactions, thanks!