Hello all,
I’m pretty new with TOAD data point, and I’m working on a query that a coworker of mine built with TOAD. This query had two sub queries, one of which connects to an ODBC connection. This is the only place in the query that it connects to the ODBC database. I can run the sub query on it’s own, and it works perfectly but when I try to run the main query it bombs out with an error saying that there is no such table in ODBC database. Am I missing something here?
Did you fully qualify the table?
select * from schema.table:
Yes, it’s fully qualified. I’m using the query builder, and I’m linking to multiple access databases and multiple tables within an ODBC connection. Unless toad isn’t fully qualifying them when they get brought in, I highly doubt it’s anything on my side. I’m in the process of rebuilding the query and it’s doing the same thing with a different table in ODBC. Is there some kind of limitation I’m running into?
It sounds like you need to use a cross-connection query builder. If you are using two different connections this would be the case. Can you post a screenshot of the SQL and error you are getting along with your connections?
I’m not at liberty to take screenshots of this, but I can describe it as best as I can. This is the error I’m receiving:
_mvdec5hga24t52nu0n2olg4btgg2ghu0: No such table in ODBC database: “xxxxl”.“csictl”.“cprt”
I don’t understand the issue. There seems to be no rhyme or reason as to why it’s not connecting.
Did you use a cross connection query builder or the regular query builder? Because regular will work when you run the subquery separately but not together.
Yes it’s cross connection query builder. It says “Cross Connection Query” in the right hand side, in a blue box as well. I can keep reproducing this error with different queries as well. Is there some kind of limitation within toad to connecting to multiple access databases? Can TOAD only connect to a few databases at a time?
Edit: This is TOAD Data Point 3.8 Professional Edition
There should be no limitation to number of tables in a Cross-Connection. To help you further I need you to open a support ticket. To move that along faster the can produce a log file that will help development see what is occurring.
Go to your desktop short cut and and " /log=all" to the command line. Restart TDP and produce the Cross-Connection error in the Query Builder. Leave the query builder open and focused and then generate a support bundle from the Help menu. include this zip file in your support case and tell the support rep to make a subcase and I will look at it.
Debbie, Just to follow up on this: I have a ticket open with Dell and it seems that we’ve identified that I cannot make cross connection queries work with pretty much any access database; it keeps giving me an ODBC connection error even if I’m not asking it to connect via ODBC. The ticket number is 3179495.
I am looking into your files on support.
i asked support to send the support case to development. In the meantime what happens if you double click on the Local Storage Connection? Does this error or is it successful? This is the internal database that performs the cross-connection execution and I see an error in your log file when it starts up.
I am able to successfully connect to the Local Storage Connection. I can reconnect multiple times with no issues. I tried rerunning my query after disconnecting and reconnecting to it, and I still received the same error.
Support and I have figured out an answer. Putting each different access database into a subquery and then linking the SQL Server to those subqueries will run without issue.
Just another FYI, support.quest.com/…/91052 – Really amplifies how painful it is to have to create subqueries.
Has any progress been made on this? TOAD is still essentially useless to me because of how slow the subquery builder is. My co-worker has version 3.6 and it works for him - albeit slowly, but it at least works. Can I downgrade to 3.6?
Different version of Toad can be installed side by side so you should be able to install TDP 3.6 without affecting your current installation.
Regard
Aleksey