Toad World® Forums

bug: automation with only one sheet in excel when script has more than one output.

Hi,

I create an “automation” package with a linked script which has two output (eg, select * from aaa, select * from bbb). in the output xls file, I only see one sheet.

thanks!

Toad for SQL server

version 6.0.0.323

Hi boby,

I tried to reproduce this issue but filed I can export two sheet ,

could you tell me the detailed steps and the version of SQL Server or something else,

Hope your reply and many thanks to you!

thank you Cathy!

I am connecting to a SQL 2008 server.

  1. Tools > Animation

  2. drag “Select to File” into the diagram above Settings

  3. open a sql in execute script setting with “Link SQL file” checked

select top 100 * from Tcompanies

select top 100 * from TContacts

  1. “Excel File” > d:\aaaaaa > Date(‘YYYY-MM-DD’)

  2. “F5” to run

I thought I will see one excel file with two sheets but I only see the output of the first select statement.

thanks!

Timestamp Message

9/25/2013 9:03:55 AM Starting Automation Script

9/25/2013 9:03:55 AM Setting up environment

9/25/2013 9:03:55 AM Script run by Toad for SQL Server 6.0.0.323

9/25/2013 9:03:55 AM Build started

9/25/2013 9:03:55 AM Compiling Script

9/25/2013 9:04:01 AM Warning: No Exception Handler. To send an Email when there is an error, change the value of Email on Error to true and enter email values.

9/25/2013 9:04:01 AM Build Completed

9/25/2013 9:04:01 AM Begin Export of File_1

9/25/2013 9:04:01 AM Connection to: devsqlcl03.aaa.local (aaa\yang), DB3

9/25/2013 9:04:01 AM Begin Export

9/25/2013 9:04:01 AM SQL to execute: select top 100 * from Tcompanies

select top 100 * from TContacts

9/25/2013 9:04:01 AM Beginning export

9/25/2013 9:04:01 AM Attempted to export 100 rows

9/25/2013 9:04:01 AM Successfully exported 100 rows

9/25/2013 9:04:01 AM Export Finished, click to view file

9/25/2013 9:04:01 AM Row count variable File_1_RCOUNT set to 100 rows

9/25/2013 9:04:01 AM Begin execution script activities

9/25/2013 9:04:01 AM Done

Hi Boby,

According your description I reproduced this issue.

The reason why you only have one sheet is that your scripts lack of go statements

You can add it then you will export two sheets.

select top 100 * from Tcompanies

go

select top 100 * from TContacts

go

I will also create a bug for this case,Toad should export two sheets even without go statements.

Thanks again!

thanks! Cathy!

by the way, I also have a problem. I don’t know the reason yet but it will be good if you know or you have experience already.

in the automation, I create one automation with 12 linked scripts running. I got error message in few scripts about something like “ID_xx_XXX” not found (my own key) and different for each script. However, I run it in the script editor and everything runs without any problem.

do you think missing “GO” causes the problem? or using block comments like /*** xxx ****/ could cause problems too? I use a lot too.

thanks

Bob