Automation Question

Toad Data Point
1

I’ve perused the Help files, but I’m having some difficulty understanding how I would go about automating the following SQL selects. The only thing that changes in each of them is the date, which I’m guessing I would use a variable of some kind for.

Currently, these are each run by hand - 2 of them are run once daily, the other is run twice daily at different times. Each is exported to Excel and saved to a shared folder, then emailed to a TO and CC distro lists from Outlook.

Can someone point me in the right direction for figuring out how to automate these? Your help is much appreciated.

Script 1: Once per day, prior to 0830, date is previous day

SELECT idst_wip.acctcorp, idst_wip.house, idst_wip.cust,
idst_wip.wstat, idst_wip.pts, idst_wip.wordate, idst_wip.wloc,
idst_wip.w1st5, idst_wip.sa, idst_wip.who, idst_wip.tcrectim,
idst_wip.schdat, idst_wip.mgt, idst_wip.ftax, idst_wip.changeopr,
idst_wip.qtaslot, idst_wip.mustdo, idst_wip.schedondate,
idst_wip.compliance, idst_wip.tcstartby, idst_wip.reschnotdone,
idst_wip.svcintrpt, idst_wip.newwproty, idst_box_inventory.bnumb,
idst_box_inventory.addleq, idst_box_inventory.mfg,
idst_box_inventory.purchdate, idst_box_inventory.loc, idst_box_inventory.bxstatus,
idst_box_inventory.addrsable, idst_box_inventory.evtcpbl, idst_box_inventory.hibit,
idst_box_inventory.outlet, idst_box_inventory.opr, idst_box_inventory.boxequip
FROM idst_wip join idst_box_inventory ON
idst_wip.acctcorp = idst_box_inventory.acctcorp AND
idst_wip.house = idst_box_inventory.house AND
idst_wip.cust = idst_box_inventory.cust
WHERE idst_wip.acctcorp IN (05705, 32007) AND
idst_wip.wstat = ‘F’ AND
idst_wip.schdat > DATE ‘2010-12-10’ AND
idst_box_inventory.bxstatus = ‘D’ AND
idst_box_inventory.fmt = ‘A’

Script 2: Once per day, prior to 0830, date is the previous day, run at the same time as script 1

SELECT acctcorp,
tech,
wstat,
RESCHNOTDONE,
Count (*),
Sum(pts)
FROM idst_wip
WHERE acctcorp IN (05705, 32007)
AND wpcddate = DATE ‘2010-12-10’
GROUP BY acctcorp, tech, wstat, RESCHNOTDONE

Script 3: Twice per day, at 1100 and 1400, date is the current day:

SELECT idst_cdw_detail.acctcorp, idst_cdw_detail.cddate, idst_cdw_detail.tech, idst_cdw_detail.dseq, idst_cdw_detail.house,
idst_cdw_detail.dcstat, idst_cdw_detail.wordrsn, idst_cdw_detail.wjob, idst_cdw_detail.pts
FROM idst_cdw_detail
WHERE idst_cdw_detail.acctcorp IN (05705, 32007) AND
idst_cdw_detail.wjob IN (1, 2, 3, 4, 7, 8) AND idst_cdw_detail.cddate = DATE ‘2010-12-12’

I realize this SQL is not the cleanest, but I didn’t write it :slight_smile:

2

Try using the date range feature in the Query Builder. Based on the date range you choose it will generate the date just prior to execution of the SQL. You can save the script to a tsm file and use in Automation. This generates a SQL statement with a TDA phrase that is replaced under the hood. (see calendar__gregorian__yesterday below) You can try just using this phrase with the SQL you have and not building the SQL in the Query Builder.

Debbie

SELECT ORDERS.ORDER_DATE
FROM QUEST_DEV.ORDERS ORDERS

WHERE (ORDERS.ORDER_DATE = calendar__gregorian__yesterday__)

[cid:image001.png@01CB9A3A.005CE620]

image001.png
image001.png833×586 21.6 KB

3

Hi Debbie,

I rebuilt one of the simplest queries in Query Builder and did as you said, and it works perfectly. I’ve saved that tsm file and have moved on to try and automate it.

Edit: My IT group came through with the SMTP settings for our Exchange setup. I am, however, getting an odd error when I test - Toad executes and exports the file successfully, then fails with attaching and emailing. Yet it can attach and email me the error log sucessfully.

Also, on the Select To File activity - is there anyway to specify the name of the file other than the default choices (which are to overwrite or append either the date or date-time to the name of the file)? Our reports go out as (for example) “Tech Progress_121310_1400.xlsx” - I can’t seem to find a way of specifying a particular output name that’s dynamic.

Debbie Peabody wrote:

Try using the date range feature in the Query Builder. Based on the date range you choose it will generate the date just prior to execution of the SQL. You can save the script to a tsm file and use in Automation. This generates a SQL statement with a TDA phrase that is replaced under the hood. (see calendar__gregorian__yesterday below) You can try just using this phrase with the SQL you have and not building the SQL in the Query Builder.

Debbie

SELECT ORDERS.ORDER_DATE
FROM QUEST_DEV.ORDERS ORDERS

WHERE (ORDERS.ORDER_DATE = calendar__gregorian__yesterday__)

[cid:image001.png@01CB9A3A.005CE620]

4

You will need to get the SMTP server address from your MIS department. The NT logid should not be necessary. We are adding code in TDA 3.0 to automatically detect the SMTP server. But you will have to supply yourself for now.

On the select to file, you can dynamically create a file name by using a variable.

See the blog post listed below. I don’t think it shows an exact example of Select to File dynamic filenames, but you can create a variable (IE: myvar) and enter this in the suffix --> _#myvar#

If will take the filename you entered and append the value of the bind var.
IE: C:\mydirectory\filename_valueOfVar.xls

http://www.toadworld.com/BLOGS/tabid/67/EntryId/498/Automation-Variables-Automation-variables-using-datasets.aspx

Debbie

5

My IT group was able to supply me with the SMTP relay information for our Exchange environment, so I’ve got that part working now.

Using your document I was able to figure out how to setup and use variables for the filename and it works perfectly - Thank you!

I do have a question, however - is there a “default” variable that will return the name of the exported file, so that I can use it as the subject of the email? If not, how would I capture this, so that I could use it. The file name is setup to be dynamic with the current date followed the the execution time in 24H format.

Debbie Peabody wrote:

You will need to get the SMTP server address from your MIS department. The NT logid should not be necessary. We are adding code in TDA 3.0 to automatically detect the SMTP server. But you will have to supply yourself for now.

On the select to file, you can dynamically create a file name by using a variable.

See the blog post listed below. I don’t think it shows an exact example of Select to File dynamic filenames, but you can create a variable (IE: myvar) and enter this in the suffix --> _#myvar#

If will take the filename you entered and append the value of the bind var.
IE: C:mydirectoryFilename_valueOfVar.xls

http://www.toadworld.com/BLOGS/tabid/67/EntryId/498/Automation-Variables-Automation-variables-using-datasets.aspx

Debbie

6

We don’t have a default variable as you mention. I have set it so the email attachments can add the files with the dynamic names. You will see the filenames with vars in the email attachment editor.

Since you know the filename and the variable I would just put them in the subject.

Subject: MyExportFile_#myvar#

This should dereference the variable at run time and put the actual file name in the subject.

7

I see what you’re saying. Well, it’s not the desired way, but I can work with that.

Are there any built-in variables that we can reference? The help file for TDA doesn’t really say one way or another.

I do appreciate your assistance - this is going to keep me from being up before the crack of dawn on weekends to run these reports :smiley:

Debbie Peabody wrote:

We don’t have a default variable as you mention. I have set it so the email attachments can add the files with the dynamic names. You will see the filenames with vars in the email attachment editor.

Since you know the filename and the variable I would just put them in the subject.

Subject: MyExportFile_#myvar#

This should dereference the variable at run time and put the actual file name in the subject.

8

Currently I do not have any built-in variables. I like the idea. Can you list what variables might be useful?

9

This is my first time messing with the automation, so I’m sure I might think of more as I go along. My boss is very happy that we can automatethis without any intervention on our part - I’ve got a test setup to try out the scheduler piece and if that works, tomorrow we’ll get the rest enabled.

Definitely file name(s), perhaps machine name, Network ID, some more Date & time functions would be nice - I ended up having to manually build mine to get what I wanted (speaking of which, that VB scripting window, it would be nice if it was resizeable).

Perhaps environment information from PC or connection data?

Debbie Peabody wrote:

Currently I do not have any built-in variables. I like the idea. Can you list what variables might be useful?

10

I like your suggestion. I have entered this enhancement as Cr80685. It won’t make it for TDA 3.0 but probably 3.1.

Thanks. Please post any other thoughts you have to improve automation.