Multiple connection strings

  • 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

  • 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-

  • 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.

  • 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

  • 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.

  • 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