ODBC Driver Error Message in Automation tool

Toad Data Point
1

I have created a query (.tsm file) which successfully logs into my database, queries two different tables within that db, and displays the correct results under the results tab.

I would now like to automate this process and have the results of the script written to a local html file on my server. I have gone to the automation section, slected “select to file” and selected the above referenced script in the “SQL Script” section, and also specified a file name and location for the html output.

When I click test, I receive the below error:

Automation Logging 12/28/2008 7:22:09 PM You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘"ODBC;DRIVER={MySQL ODBC 3.51 Driver};SERVER=xxxxxxxxxxxx.com;DATABASE=sop_s’ at line 13

However, if I do exactly the same process as above with a sql script that just queries one table in the same database, it works.

The difference between the query that Toad writes for the one table and two table queries is that in the two table code, it has a line (line 13) per the below:

FROM {{Odbc(“DRIVER={MySQL ODBC 3.51 Driver};SERVER=xxxxx.com;DATABASE=sop_sop;UID=sp_rp;PORT=3306;OPTION=3;PWD={0};,B9949xxx28250EB”)}}.jos_users jos_users, {{Odbc(“DRIVER={MySQL ODBC 3.51 Driver};SERVER=xxxxx.com;DATABASE=sop_sop;UID=sp_rp;PORT=3306;OPTION=3;PWD={0};,B9979xxx728250EB”)}}.calculations calculations

The one table query just has: FROM sop_sop.jos_users jos_users

Is anyone able to help me with this error please?

Thanks

2

the query that Toad writes for the one table
How did Toad write this query for you, do you mean you used the Query Builder?

If so, do you see in the query builder a slight watermark 'Heterogenous Query'?

have you tried manually writing the query yourself with just standard mysql sql syntax that does not have the toad-inserted ODBC syntax? You would then save this to file and reference the file in your execute sql step in your automation script...

3

Thanks. I spoke to someone in support and they said “This can happen if you start working on a heterogeneous query (tables from different schemas/platforms in one query) and then clear the tables and drag new tables into the query builder window.”

Basically its a bug which will be fixed in the next version. In the mean time, I had to recreate my query