I am using Toad for Data Analysts 2.7 and my heterogeneous query is not working since I am querying against OBI, DB2 and SQL Server. What is the best way to combine results from these 3 sources? I could exported results into Excel and I could import all 3 files into SQL Server to write a final query and run it from there. But this report needs to run every week so it does not look like a very efficient way to handle it. Any suggestions anyone? version 3.0 is not available in my company for now.
Thanks.
I would still suggest using the 3.0 Pro version for querying three data souces. But you have mentioned that you do not have this right now.
You can automate the following Import into SQL Server.
Create a Saved Import Template that uses a Query from each of the sources. You would make one for each data source.
For example, have your SQL server connection be the current connection. Open up the Import Wizard. Chosse, Add Query. Change the connection type to DB2. Enter your query. Step throught the wizard and enter the table where you want the data to go to. Change any data types if needed and configure to truncate the table each time or append. Save the template. Here we are connecting to DB2, querying and inserting into SQL Server in one step.
Add these three templates to an automation script along with the new query that joins the three tables in SQL Server.
Not necessarily eligent but automatible.
Debbie