January 19, 2004 at 7:32 am
I'm trying to set up a stored procedure where I connect accross two different servers. One is to fetch the information based on the (lets say date) and the other one then inserts into another database on another server. Currently I'm doing it in an asp page, but it's obviously terrible low on efficiency .
for example:
open connection A and B
fetch 80 000 rows(server A)
loop starts
Insert 40 000 rows + new info(server B)
Loop ends
close connection A and B
Linked servers and Replication are out of the question as these Queries/connections are dynamic (entered by the user at runtime)
Any Help/advice would save me from a career change
January 19, 2004 at 8:32 am
Kevin, Some questions to help me understand the environment more...
1) When you say dynamic SQL, do you mean there are different tables involved, different columns, or just different selection criteria?
2) Does the second database (where inserts take place) have the ability to connect to the first one?
In the easiest scenario (different selection criteria), you could have the first connection put the data into a table with a code unique to the current process (GUID or Random # as a key). The second connection could query from the first (of course this assumes the second connection has a database connection to the first).
Table variables may also be an option for you.
Guarddata-
January 19, 2004 at 2:08 pm
The only way I can think of to do this would be to use dynamic sql inside your sp. Even though the user specifies different databases couldn't you still have those DB's alread linked? I can't imagine allowing a user to link to just any machine. Sounds like a security nightmare!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 22, 2004 at 2:34 pm
I think you could use DISTRIBUTED TRANSACTION
for example
USE pubsGOBEGIN DISTRIBUTED TRANSACTIONUPDATE authors SET au_lname = 'McDonald' WHERE au_id = '409-56-7008'EXECUTE remote.pubs.dbo.changeauth_lname '409-56-7008','McDonald'COMMIT TRANGO
bondada
January 22, 2004 at 2:41 pm
You're correct that a dristributed transaction could be used. So might SET XACT_ABORT ON. The biggest problem is that these transactions are based on a dynamically entered value from the user so the whole sql statement has to be dynamic.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 28, 2004 at 12:51 am
thanks guys!
I've never used either... but will investigate
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply