SQL Query in TI with multiple databases

[deepakjain2020]

Hi All,

We have a SQL query which uses tables from two databases.
We create an ODBC connection, which connects to single database.

Do we have any solution to use both databases in single TI process?
The thought which came to my mind is to move specific tables into single database, but if anybody have any idea of using multiple database in single query in TI will be helpful.

If any more information is required, please let me know.

Regards,
Deepak Jain

[sachin]

What is the underlying server? Are they with the same provider? Are the 2 DBs on the same server?

I never encountered this in TM1. However, I have written queries for reconciliation that span across DBs (but they were all on same MS SQL Server). You just use the syntax
Select T1.*, T2.*
FROM DB1.dbo.T1 as T1
INNER JOIN DB2.dbo.T2 as T2 ON T1.aaa = T2.bbb

I am assuming this will work in your TI too, given the service account has permissions to the requisite tables. HTH.

[deepakjain2020]

Hi Sachin,

Yes, both DB's are on same SQL server.
I will give try to know whether it will work or not.

The only concern is, when we create an ODBC we refer to only one DB and not both.
So does Dat Source referring to single Database, will allow to access multiple DB's?

Regards,
Deepak jain

[tomok]

The only concern is, when we create an ODBC we refer to only one DB and not both.
So does Dat Source referring to single Database, will allow to access multiple DB's?

When you create an ODBC connection on a Windows machine to an MSSQL server you are creating a connection to the SQL server, not a specific database. I think the reason the Microsoft ODBC wizard asks for a specific database is so that it can test the connection when you are done. As long as the ID you are using to connect to the server has the appropriate security to both databases the approach sachin suggested should work.

[deepakjain2020]

Thanks Tomok.

I will give a try.

Regards,
Deepak Jain