Post reply

how to connect to 2 SQL Servers from a DTS package?

  • July 23, 2004 at 4:43 am

    #164557

    hi

    have a DTS pacakge, the job of which is to efficently transfer (INSERT....DELETE) data from one SQL Server database machine to another. The problem is that I will be using sa login for the source, and windows authentication for the other.

    Not sure how one can set up 2 logins at once? If I set the sa username/passwords the same for both servers (and then reference the source and destination by [server].[database].

    ) it works perfect!

    Any tips

    thanks

    jon

  • July 23, 2004 at 5:03 am

    #515830

    Create either 2 different UDL connections or 2 different SQL Server OLE DB connections.

    Each of these can be configured differently.  Granted with this approach you will need to write either 1 or several different sp's to be called by the Workflow task instead of using the copy SQL objects.  However, it will allow you to connect as sa to a server, windows to another, etc...

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 23, 2004 at 5:47 am

    #515835

    hi

    thanks for the tip. however, part of my SQL query involves a SELECT from one server and an INSERT into another server (using a sub query). If i use 2 or more SPs, will this not prevent me from doing so?

    Kind regards

    jon

  • July 23, 2004 at 5:14 pm

    #515971

    try setting up the 2 servers as "Linked Servers" to each other or from just one side.  This way you could do

    SELECT * FROM LinkedServerName.DatabaseName.Owner.Table

    This would allow you to get the information from the 2nd server from the 1st

    You can find a lot of information about Linked Server in BOL and on-line



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

Viewing 4 posts - 1 through 3 (of 3 total)

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