Multiple connection strings

Question

Multiple connection strings

kevin-66223

Valued Member

Points: 63
More actions
January 19, 2004 at 7:32 am

#108839

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

guarddata SSCertifiable Points: 6583 More actions · Answer 1

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-

gljjr SSChampion Points: 10080 More actions · Answer 2

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.

rangab Old Hand Points: 303 More actions · Answer 3

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

gljjr SSChampion Points: 10080 More actions · Answer 4

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.

kevin-66223 Valued Member Points: 63 More actions · Answer 5

thanks guys!

I've never used either... but will investigate