Query result is not export to Excel when running automation script

Toad Data Point & Toad Intelligence Central
1

The query is as the following:

SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = ‘Customer’
order by COLUMN_NAME

The expect result should be as the following in result excel file

TABLE_NAME COLUMN_NAME
Customer CustomerAccountID
Customer CustomerAdhocEmail
Customer CustomerBusinessEmail
Customer CustomerBusinessNumber

The actual result is as the following:

TABLE_NAME COLUMN_NAME

Steps to re-create:

  1. Select the query
  2. Select export excel file
  3. Uncheck “overwrite” check box
  4. Open “Advanced Properties” window and then check "Use column name for a header row
  5. Run the automation scripts

Query result is not export to Excel when running automation script

2

Hello Blair800,

Thank you for your feedback - I am looking into this problem but could you please answer my below questions?

  • are you using a MySQL connection to run this query?
  • i have tried to simulate this but it works for me - can you please ensure that you are using exactly same connection in your automation script as in the query editor?
  • also please check that you are using the same database — you can include additional column ‘TABLE_SCHEMA’ in your query which will display name of the database/schema the table column belongs to
  • if that doesn’t work for you, could you please open and execute the query and then send me the below files to my address: martin.holkovic@quest.com
    • a screenshot of the Navigation manager that shows the active connection
    • a screenshot of the query editor itself that shows the selected database dropdown control
    • your automation script itself
    • export your MySQL connection that you are using
      I will try to compare the connection properties to see if they match just to be sure

Thank you,

Martin

3

Hi Martin,

Thank you ! I have sent the screenshot and automation file to you

4

Thank you,

Please note that the Editor window shows the current database dropdown but in the automation script there is no control for that - so the default database specified from the Connection properties is used which i assume is master. This means that the query you’ve exected (select xyz from information_schema.columns…) was querying table structures from the master database which does not contain definitions for the tables listed in the “where table_name in (…)” clause.

To fix this you have to either manually specify the database name in the FROM clause (i.e. [fpmdb-msit].information_schema.columns) or open the connection properties and select the FPMDB-MSIT item from the “Database” dropdown list at the bottom.

Hope this helps,

Martin

5

Thank you Martin !

It works after selecting specify the database name in the FROM clause