# Setting Variables in Automation Issue -- Behaves Differently Than in Editor or .TDR File **URL:** \ **Category:** Toad Data Point & Toad Intelligence Central\ **Created:** [December 24, 2013, 3:11pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171 "2013-12-24T15:11:32Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![eric.chilenskas](https://avatars.discourse-cdn.com/v4/letter/e/4da419/32.png) [@eric.chilenskas](https://forums.toadworld.com/u/eric.chilenskas)\ **Post date:** [December 24, 2013, 3:11pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/1 "2013-12-24T15:11:32Z")
I have the darndest issue. I have a .tdr I’m trying to automate. When I run the SQL query behind it, it runs fine. When I refresh the .tdr itself with new values for the bind variables, it runs fine. When I put it in an automation sequence, I get an “invalid number” error. There’s something different about how the automation defines & passes variables than the Bind Variable dialogue used in refreshing a standalone .tdr. Here’s the section of problematic code: SELECT xxxx from yyyyy where TR.D\_LST\_PRM\_CNTY\_TYP IN (&TCOUNTY\_GRP) I can pass a list of counties char(3) to &TCOUNTY\_GRP and it will run correctly as a solo .sql query or solo .tdr, but when I set the value (as a string) in the Set Variable function in Automation, I get “Invalid Number.” I’ve tried setting it as a number, using to\_char inside the code; I’ve tried with and without quotes when setting the value, quotes around the parameter name, etc. Hard coding the list of counties is possible, but it’s a lot of reports 64 x 6 to be exact. Anyway, insight would be a great present for when I come back from the holiday tomorrow! Happy holidays, all! ---
**Author:** ![eric.chilenskas](https://avatars.discourse-cdn.com/v4/letter/e/4da419/32.png) [@eric.chilenskas](https://forums.toadworld.com/u/eric.chilenskas)\ **Post date:** [December 26, 2013, 1:08pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/2 "2013-12-26T13:08:29Z")
Here’s what’s happening in the Automation Set Variables versus the Bind Variables dialogue box: In Bind Variables, entering &TCOUNTY\_GRP as ‘01’,‘02’,‘03’ correctly fills in the SQL parameter as IN (‘01’,‘02’,‘03’) – a list of character codes to match char(3) codes in the table. Entering ‘01’,‘02’,‘03’ as a String in the Set Variable activity results in IN (‘01,02,03’) which doesn’t work. Setting 01,02,03 without quotes results in an error from the commas. I want to be able to use the set variable activity and set my parameter &TCOUNTY\_GRP so that it is correctly represented in the SQL IN statement literally as (‘01’,'02,‘03’). Is there some way to concatenate or escape characters in the Set Variable activity to make this happen? ---
**Author:** ![eric.chilenskas](https://avatars.discourse-cdn.com/v4/letter/e/4da419/32.png) [@eric.chilenskas](https://forums.toadworld.com/u/eric.chilenskas)\ **Post date:** [December 26, 2013, 1:12pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/3 "2013-12-26T13:12:38Z")
Sorry, I got some of that wrong. Entering 01,02,03 into the Set Variable activity results in ‘01,02,03’ which doesn’t work–need a comma separated list of chars. Entering ‘01’,‘02’,‘03’ into the Set Variable activity results in an error “cannot be converted to a string, expecting EOF.” ---
**Author:** ![eric.chilenskas](https://avatars.discourse-cdn.com/v4/letter/e/4da419/32.png) [@eric.chilenskas](https://forums.toadworld.com/u/eric.chilenskas)\ **Post date:** [December 26, 2013, 2:20pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/4 "2013-12-26T14:20:06Z")
To be clear: The Set Variable activity treats inputs differently than the Bind Variable dialogue. This is a problem, because when developing a .tdr, you’re working with the Bind Variable dialogue box, and then you find out your .tdr doesn’t work in Automation with Set Variable. The issue is comma-separated lists of characters can be entered into the Bind Variable dialogue as a parameter inside an IN (¶m\_name) statement and run correctly. In the Set Variable activity, the same parameter entry fails. A list entered as ‘01’,‘02’,'03 in the Set Variable activity will throw an error, “cannot be converted to string.” ---
**Author:** ![eric.chilenskas](https://avatars.discourse-cdn.com/v4/letter/e/4da419/32.png) [@eric.chilenskas](https://forums.toadworld.com/u/eric.chilenskas)\ **Post date:** [December 26, 2013, 2:36pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/5 "2013-12-26T14:36:32Z")
It’s Toad Data Point 3.4.0.2038 btw. ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [January 6, 2014, 11:46am UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/6 "2014-01-06T11:46:41Z")
We had a bug with the SQL Plus bind vars in automation. We made a patch that includes a fix for this. Please download either base or pro depending on your licesnse. [community-downloads.quest.com/…/ToadDataPoint\_Base\_3.4.0.2059.zip](http://community-downloads.quest.com/toadsoft/tda/ToadDataPoint_Base_3.4.0.2059.zip) [community-downloads.quest.com/…/ToadDataPoint\_pro\_3.4.0.2059.zip](http://community-downloads.quest.com/toadsoft/tda/ToadDataPoint_pro_3.4.0.2059.zip) ---
**Author:** ![lp187q](https://avatars.discourse-cdn.com/v4/letter/l/ccd318/32.png) [@lp187q](https://forums.toadworld.com/u/lp187q)\ **Post date:** [February 7, 2015, 1:56pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/7 "2015-02-07T13:56:24Z")
Hello Good Afternoon! I am having the same issue and I’ve been checking the Forums and Tech Support for TDP for the last two days non-stop and finally found someone who could explaing my issue. I’m having this exact issue (BTW, I’m a newbie in SQL, just a user who want to make his life easier through automation :)). I tried installing the package above and the app didn’t allow me doing it because I already have the package. I’m currently using TDP 3.5.0.2926. I hope you can help me. (Debbie, I’m impressed how you and your team have responded so many difficult situations from us, the users :). Thanks!) ---
**Author:** ![GregDavis11009](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gregdavis11009/32/122_2.png) [@GregDavis11009](https://forums.toadworld.com/u/GregDavis11009)\ **Post date:** [February 10, 2015, 8:17am UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/8 "2015-02-10T08:17:01Z")
Have you tried using 2 or 3 single quotes instead of one. ‘‘01’’, ‘‘02’’,’‘03’’ or ‘’‘01’’’,’’‘02’’’,’’‘03’’’ or maybe ‘‘01’’’, ‘’‘02’’’, ‘’‘03’’ (2 on the ends with 3 in the middle). Not sure because it is going to put in single quotes of its own. ---
**Author:** ![GregDavis11009](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gregdavis11009/32/122_2.png) [@GregDavis11009](https://forums.toadworld.com/u/GregDavis11009)\ **Post date:** [February 10, 2015, 8:26am UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/9 "2015-02-10T08:26:27Z")
Try this one: select ‘’‘01’’,’‘02’’,’‘03’’’ as TestString (3 single quotes on the ends with 2 on the middle ones). The select statement works that way but if Toad puts in its own quotes then all 2 single quotes is may be the way to go. ---
**Author:** ![lp187q](https://avatars.discourse-cdn.com/v4/letter/l/ccd318/32.png) [@lp187q](https://forums.toadworld.com/u/lp187q)\ **Post date:** [February 11, 2015, 12:01pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/10 "2015-02-11T12:01:25Z")
Thanks Greg! I’ll check this and will let you know. Somehow I did not get a notification email for your response. Thanks Again! ---
**Author:** ![lp187q](https://avatars.discourse-cdn.com/v4/letter/l/ccd318/32.png) [@lp187q](https://forums.toadworld.com/u/lp187q)\ **Post date:** [February 11, 2015, 12:24pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/11 "2015-02-11T12:24:07Z")
Greg, Sadly this did not work. I tried all your inputs. I thought I should be more specific on what I'm doing: My automated query calls my variable this way: CELL in (:Cell) and the input should be a series of string values separated by comma, such as: 'ABCD','EFG','HIJ'.... (usually several tens or more, including first and last single quotes). Below a screenshot of the exact error I get. ![TDP Error Automation.jpeg](https://global.discourse-cdn.com/quest/original/2X/b/b2115298c2bf47a87ffdb66e99808d154de17f34.jpeg) On my regular queries I usually use: CELL in ('&&Cell') with the following input ABCD','EFG','HIJ (and works OK) . Please note that I don't use the first or last single quotes on my regular ToadDP (or Toad for Oracle) I hope this helps. ---
**Author:** ![GregDavis11009](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gregdavis11009/32/122_2.png) [@GregDavis11009](https://forums.toadworld.com/u/GregDavis11009)\ **Post date:** [February 11, 2015, 3:53pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/12 "2015-02-11T15:53:51Z")
Okay try this then: ABCD’,‘EFG’,'HIJ The backslash is an escaper character that forces the next character to be interpreted as just a character. ---
**Author:** ![lp187q](https://avatars.discourse-cdn.com/v4/letter/l/ccd318/32.png) [@lp187q](https://forums.toadworld.com/u/lp187q)\ **Post date:** [February 11, 2015, 4:59pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/13 "2015-02-11T16:59:35Z")
Greg, I really apologize, I'm not that familiar with TDP and it seems to be very picky on the input box for variables on the automation scripts. The Good news: the input box for the variable does not give an error now. The bad news, the AUTOMATION does not run yet. Below a screenshot of the errors. As a reminder my statement is CELL in (:CELL) so I need to use the first and last single quote ![TDP Error Automation 2.jpeg](https://global.discourse-cdn.com/quest/original/2X/8/805f0879f2174d6e354c28a5ae418c0b8b722f35.jpeg) Thanks for your responses. ---
**Author:** ![IgorM](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/igorm/32/116_2.png) [@IgorM](https://forums.toadworld.com/u/IgorM)\ **Post date:** [February 11, 2015, 6:02pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/14 "2015-02-11T18:02:40Z")
Yes, escaping single quote should work… but it doesn’t. I’m investigating it right now. Igor. ---
**Author:** ![eric.chilenskas](https://avatars.discourse-cdn.com/v4/letter/e/4da419/32.png) [@eric.chilenskas](https://forums.toadworld.com/u/eric.chilenskas)\ **Post date:** [February 12, 2015, 10:09am UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/15 "2015-02-12T10:09:39Z")
Hi, I’m the originator of this thread, and it still does not work for me either. Having an escape character is fine, but I would rather the Automation behave the same as the SQL editor and the .TDR files for all inputs. It’s frustrating to write SQL, make a report file, and then have it fail in automation. I believe Debbie Peabody told me there was a ticket for this (correctly passing lists in Automation). Additionally, in automation, you can’t pass more than one parameter to an Excel file, but you can in the SQL editor and TDR file. Automation throws a “missing parameter to query” error from Excel, though the TDR and SQL will work for the same Excel. ---
**Author:** ![IgorM](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/igorm/32/116_2.png) [@IgorM](https://forums.toadworld.com/u/IgorM)\ **Post date:** [February 13, 2015, 8:59am UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/16 "2015-02-13T08:59:47Z")
We will investigate it further. I’ve created QAT-4192 to track this issue. Igor. ---
**Author:** ![lp187q](https://avatars.discourse-cdn.com/v4/letter/l/ccd318/32.png) [@lp187q](https://forums.toadworld.com/u/lp187q)\ **Post date:** [February 24, 2015, 9:38am UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/17 "2015-02-24T09:38:16Z")
Hello, Good Morning! I was wondering what the status is for this request? Thanks in advance. ---
**Author:** ![IgorM](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/igorm/32/116_2.png) [@IgorM](https://forums.toadworld.com/u/IgorM)\ **Post date:** [February 24, 2015, 2:00pm UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/18 "2015-02-24T14:00:28Z")
This has been fixed. See the fix in the next beta. Thanks, Igor. ---
**Author:** ![eric.chilenskas](https://avatars.discourse-cdn.com/v4/letter/e/4da419/32.png) [@eric.chilenskas](https://forums.toadworld.com/u/eric.chilenskas)\ **Post date:** [February 25, 2015, 9:16am UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/19 "2015-02-25T09:16:19Z")
That’s awesome. This was my number one issue. I look forward to it. ---
**Author:** ![lp187q](https://avatars.discourse-cdn.com/v4/letter/l/ccd318/32.png) [@lp187q](https://forums.toadworld.com/u/lp187q)\ **Post date:** [March 26, 2015, 9:27am UTC](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171/20 "2015-03-26T09:27:03Z")
Igor, My excuses, I’m a newbie in Toad. Where do I find the Beta? or latest release(which one) that has this fixed? Thanks again. [Next page](https://forums.toadworld.com/t/setting-variables-in-automation-issue-behaves-differently-than-in-editor-or-tdr-file/28171.md?page=2)