I used to work with Toad for SQL Server 5.8 and I have been asked to migrate my automation files to TDP 3.7 (version 3.7.0.973). In my Toad for SQL Server 5.8, I had automated the sending of an email that picked the most current Excel file and added the current date on the email’s subject file. I accomplished this by adding the following syntax to the “attachment’s” name and in the subject line:
Attachment: Daily_Report_for_#yyyy-MM-dd#.xlsx
Email subject: Group Census for #yyyy-MM-dd#
With this setup, my query would run, adding the current date to the file name (Daily_Report_for_2015-09-23.xlsx). This file is then selected by the email setup step because of the syntax and the email is sent out with the subject line: Group Census for_2015-09-23.
Now in TDP 3.7, I cannot get this to run properly. I receive an error "Toad has detected some symbols within the expression indicating you might be using bind variables. Please define the following variable(s): yyyy-MM-dd.
How can I work around this to make this work again?
You might need to create a variable and set it to todays date formatted as year-month-day. If you can’t get the date formatted as you want with Toad functions you can always set the variable to a SQL query result where you can format getdate(), sysdate, current_date, etc. any way you want. Then use whatever you named that variable as [tag:variableName]# in place of the [tag:yyyy]-MM-dd#.
This is exactly what needs to be done here. Create a string type variable (let it be TodaysDate) with the value of:
Date(‘YYYY-MM-DD’)
Put this activity in the script before the 1st activity that should use it. Btw, you can use #TodaysDate# as a suffix for your report in your Select to File activity instead of predefined Date(‘YYYY-MM-DD’) suffix.
Use something like
Group Census for_#TodaysDate#
as a subject line of your email.
This script change is needed here because we refactored variables in Automation since the version of it included in Toad for SQL Server 5.8. We tried to do our best to provide backwards compatibility. E.g., if you open old script in TDP 3.7 Automation designer, you should see that suffix of you report is automatically changed. Also, report name in the email attachment was changed accordingly as well. Unfortunately 100% backwards compatibility cannot be guaranteed. This is the reason you need to change your script(s) manually.