Post reply

copy data from one server to another

  • May 6, 2009 at 8:11 am

    #95136

    Hi, we have some asp code right now that transfers contents of three tables from one server to another. Here is how it works right now:

    if condition1 then

    rsDBorigin = DBorigin("select...FROM table1..")

    sql = "......" & rsDBorigin("col1")

    sql = sql & rsDBorigin("col2")

    set rsDBdestination = DBdestination.Execute(sql)

    if conditon2 then

    rsDBorigin = DBorigin("select...FROM table2..")

    Do While (NOT rsDBorigin.EOF)

    copy table2 record to destination server

    copy any records from table3 referencing a key in table2 to destination server

    Loop

    Now we need to convert the whole process into a sql server transaction, to make sure all records in three tables get copied completely, or not copied at all.

    Using fast string is kind of ugly. But if not, I have to use linked server calls, which I prefer not to. Is there a better way to get it done?

    Thanks!

    Kathleen

  • May 6, 2009 at 8:27 am

    #989581

    duplicate post..................;-)

  • May 6, 2009 at 8:37 am

    #989594

    Yep. Sorry. The first one was accidentally sent before being completed.

  • May 6, 2009 at 1:26 pm

    #989892

    Just to ensure, if you say "Linked server" is not an option. Does this include "OPENROWSET"?

  • May 6, 2009 at 1:45 pm

    #989902

    Yes. openrowset is not ideal either.

  • May 6, 2009 at 1:49 pm

    #989903

    So, if your application currently works, why not just a "BEGIN TRANSACTION" at start and a "COMMIT TRANSACTION" after all was done?

    Another approach, to avoid a long time lock might be:

    * Write all into another (maybe temporary) table

    * If all worked fine use "INSERT INTO Destination SELECT ... FROM TempTable"

    Greets

    Flo

  • May 6, 2009 at 2:02 pm

    #989914

    Currently in the asp code, its using two connection strings, one, which is used to select the records out of, and the other one, is used to do the insert into the destination server. Unless using linked server, 'open tran... committ' wouldn't work.

    Where would the temp table be stored? On the origin or detination? Either way, it involves remote query call, is that right?

    Thanks!

  • May 6, 2009 at 2:24 pm

    #989941

    Hi

    Because you said that neither linked server nor openrowset is an option I think there is no other way than a server-client-server transfer.

    * Create a temp table (or a stateful one) on the destination server

    * Open a server side cursor for your select statement on destination database

    * Fetch all data row-by-row into client and insert into your destination temp-table

    * When done copy in one bulk into your real destination table

    By the way, what about BCP and CSV files? 🙂

  • May 6, 2009 at 3:05 pm

    #989997

    Thanks! I think what you suggested might work. I will give it a try.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply