Tech Off Thread

6 posts

Table shouldn't be dropped if no connection

Back to Forum: Tech Off
  • User profile image


    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

    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

    Have you considered publish/subscribe replication?

  • User profile image
  • User profile image

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

  • User profile image

    I'll try the transactions, thanks!

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.