Select statement using multiple database connections

Archived Topics
1

Hi!

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 pm.id = pms.productmixid
where ss.datecancelled is null and ss.recipeid = {Root Container.id} 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,
Chris

2

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.

3

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

Chris