Toad World® Forums

Query result is not export to Excel when running automation script

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

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

Hi Martin,

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

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

Thank you Martin !

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