SQL/ASP: Recordset from two different servers with vbscript

Posted on 2009-04-16
Last Modified: 2012-05-06
I have two different databases (both MS SQL Server) where I search for employees:

   conn1.ConnectionString = (connectionstring to db on server 1)
   conn2.ConnectionString = (connectionstring to db on server 2)
   sqlstr="SELECT FROM db_on_server1
   WHERE IN (SELECT FROM db_on_server2 WHERE ...)"
   Set rs = Server.CreateObject("ADODB.Recordset") sqlstr, conn1
Works fine when the two databases was on the same SQL server with just one connection string, but now one of them was moved to another server.
Is it possible to subquery like above, when the subquery targets a db on another server, or is there another way of doing this?
Question by:fmsol
    LVL 39

    Accepted Solution

    You would have to create a linked server between 1 and 2 so that queries on server 1 can access databases on server 2.
    LVL 25

    Expert Comment

    Nominate one server as a remote and setup a linked server in the other to this remote server. Then use the four part naming convention -


    to reference the remote table in the subquery..
    LVL 25

    Expert Comment

    Sorry Brandon, didn't see your post when I started responding to this question..
    LVL 39

    Expert Comment

    No big deal.  You were typing while I was.  

    Author Closing Comment

    Thank you both!

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now