-
Title
Error attempting to execute a heterogeneous query: "ERROR [HY000] [Microsoft][ODBC Microsoft A -
Description
Executing a heterogeneous query that includes an Oracle and MS SQL Server tables results in the following error: "ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] ODBC--call failed."
-
Cause
The Microsoft ODBC connection that is used to establish the Oracle connection, cannot be made on that machine.
-
Resolution
WORKAROUND 1:
Specify the ODBC default driver under Tools | Options | Database... choose the database(s) you're working with, and select a different "ODBC Default Driver" from the drop down.i.e. If you're working with Oracle, from the drop down, select "Oracle in OraClient10g_home1"
After the changes are made and applied, restart TDA and test your heterogeous query in query builder again.
WORKAROUND 2:
Toad uses Microsoft ODBC driver connectivity as default driver when it is attempting to execute heterogeneous queries. Error occurs if this connection cannot be made. Use Oracle ODBC driver connectivity to establish the connection. To do so, perform the following:
1. Connect to Oracle database using the Oracle ODBC drive*
2. Create the query in Query Builder using the tables from this connection
3. Send the query to Editor
4. Ensure the ODBC entry has the correct driver information in the query, otherwise edit the Query and replace the Microsoft ODBC text with the Oracle ODBC information
*Details are shown below
STATUS:
Waiting for fix in a future release of Toad for Data Analysis. -
Defect ID
CR 47682 -
Additional Information
To create an new connection using a different ODBC driver
1. In Connection Manager, right-click on ODBC and select New | Connection
2. Select a driver from Data Source Name drop down
3. If driver is not displayed, then Select "Open ODBC Data Source Administrator" icon next to the DSN drop down
4. Select ADD button to add the desired driver, enter the required information and test the connection
5. Close the Connection Manager and reopen it
6. Right-click on ODBC and select New | Connection
7. The new driver will appear under Data Source Name drop down