Select statement using multiple database connections


I have a project that needs to select / join from tables that reside in separate Database Connections. In the query browser, I can select from the various tables, from each DB, when the “connection” dropbox has the proper database connection selected. For instance, when the “SQLServer” connection is selected, I can do a select * from productmixsyspro (a table that resides in the SQLServer DB connection). I can’t select * from MixJobs which is defined on a different DB connection without selecting the correct DB connection (SysproScadaInterface).

What I need to do is have a select statement that can join data between these two connections…

As an example:
Select s.sysprojob,s.volrequired,s.RemainingNeed,s.datetostart from
(Select ss.sysprojob,ss.volrequired,(ss.volrequired - coalesce(sum(pm.batchsize),0)) as RemainingNeed,ss.datetostart
from SysproScadaInterface.dbo.sysproschedule ss left outer join SQLServer.dbo.productmixsyspro pms on ss.sysprojob = pms.sysprojob
left outer join SQLServer.dbo.productmix pm on = pms.productmixid
where ss.datecancelled is null and ss.recipeid = {Root} group by ss.sysprojob,ss.volrequired,ss.datetostart) as s
where s.RemainingNeed >0

The table sysproschedule resides in a database defined by the connection SysproScadaInterface
The table productmixsyspro resides in a database defined by the connection SQLServer.

Any thoughts, comments, “how to examples” would be greatly appreciated!

Thanks in advance,

If you are talking about Microsoft SQL servers and if those SQL servers are able to “talk” to each other I’d be making a Linked server connection at the SQL server level on the first server.

Your query would then become:

SELECT s1.Column [S1 Col], s2.Column [S2 Col] --, etc... FROM server1.database.schema.table1 S1 {INNER|OUTER|LEFT|RIGHT} JOIN server2.database.schema.table2 S2 ON S1.someKeyCol=S2.someKeyCol WHERE someWhereClause...

We use these linked servers frequently across our WAN for reading data, for writing data I’d be keeping the tables on a single server instance (one less variable to consider :smiley: )

Oh, and make sure you have the TCPIP protocol enabled on the SQL servers (+the SQL Browser service enabled + appropriate firewall rules as applicable) - this is crucial for getting the linked server set up. You can do all of that using MSSMS (Microsoft SQL Server Management Studio) if you are a DB Admin. If not, get your DB Admin to set it up for you.

Thanks for the reply… I’ll talk to the DB admin and see if this is possible in our environment.