# Select statement using multiple database connections

**URL:** <https://forum.inductiveautomation.com/t/select-statement-using-multiple-database-connections/8355>\
**Category:** Archived Topics\
**Created:** [April 23, 2014, 8:38pm UTC](https://forum.inductiveautomation.com/t/select-statement-using-multiple-database-connections/8355 "2014-04-23T20:38:52Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Damselfly](https://avatars.discourse-cdn.com/v4/letter/d/8491ac/32.png) [@Damselfly](https://forum.inductiveautomation.com/u/Damselfly)\
**Post date:** [April 23, 2014, 8:38pm UTC](https://forum.inductiveautomation.com/t/select-statement-using-multiple-database-connections/8355/1 "2014-04-23T20:38:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ISDProj](https://avatars.discourse-cdn.com/v4/letter/i/58956e/32.png) [@ISDProj](https://forum.inductiveautomation.com/u/ISDProj)\
**Post date:** [April 23, 2014, 9:26pm UTC](https://forum.inductiveautomation.com/t/select-statement-using-multiple-database-connections/8355/2 "2014-04-23T21:26:54Z")

</div>

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 😃 )

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.

---

<div class="post-metadata">

**Author:** ![Damselfly](https://avatars.discourse-cdn.com/v4/letter/d/8491ac/32.png) [@Damselfly](https://forum.inductiveautomation.com/u/Damselfly)\
**Post date:** [April 24, 2014, 1:28pm UTC](https://forum.inductiveautomation.com/t/select-statement-using-multiple-database-connections/8355/3 "2014-04-24T13:28:53Z")

</div>

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

Chris