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?
fmsolAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.