[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

SQL/ASP: Recordset from two different servers with vbscript

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)
   conn1.Open
   conn2.open
      
   sqlstr="SELECT employee.name FROM db_on_server1
   WHERE employee.id IN (SELECT persons.id FROM db_on_server2 WHERE ...)"
      
   Set rs = Server.CreateObject("ADODB.Recordset")
   rs.open 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?
0
fmsol
Asked:
fmsol
  • 2
  • 2
1 Solution
 
BrandonGalderisiCommented:
You would have to create a linked server between 1 and 2 so that queries on server 1 can access databases on server 2.

http://msdn.microsoft.com/en-us/library/ms190479.aspx
0
 
reb73Commented:
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 -

 [server].[database].[owner/schema].[tablename]

to reference the remote table in the subquery..
0
 
reb73Commented:
Sorry Brandon, didn't see your post when I started responding to this question..
0
 
BrandonGalderisiCommented:
No big deal.  You were typing while I was.  
0
 
fmsolAuthor Commented:
Thank you both!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now