May 6, 2009 at 8:11 am
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
duplicate post..................;-)
May 6, 2009 at 8:37 am
Yep. Sorry. The first one was accidentally sent before being completed.
May 6, 2009 at 1:26 pm
Just to ensure, if you say "Linked server" is not an option. Does this include "OPENROWSET"?
How to Post Data/Code to get the best Help How to Post Performance Problems
May 6, 2009 at 1:45 pm
Yes. openrowset is not ideal either.
May 6, 2009 at 1:49 pm
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
How to Post Data/Code to get the best Help How to Post Performance Problems
May 6, 2009 at 2:02 pm
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
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? 🙂
How to Post Data/Code to get the best Help How to Post Performance Problems
May 6, 2009 at 3:05 pm
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