Tech Off Thread

6 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Table shouldn't be dropped if no connection

Back to Forum: Tech Off
  • User profile image
    dotnetjunkie

    Hi,

    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!

  • User profile image
    itit

    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.

  • User profile image
    Maurits

    Have you considered publish/subscribe replication?

  • User profile image
    DCMonkey
  • User profile image
    ben2004uk

    Transactiosns and temp tables my friend........persoanlly I think I would just go with transactions, but temp tables might be handy

  • User profile image
    dotnetjunkie

    I'll try the transactions, thanks!

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.