Loop Dataset - How To?

Toad Data Point
1

Greetings,

I’ve got a new request that I believe would be perfect to use with the Loop Dataset activity in TDP, but I’m having some trouble fitting all the pieces together. This is the first time I’ve used this activity, so I’m sure that’s a large part of it.

To summarize: I have a list of values that are returned by a query; I then need to take that list and perform the same action for every value on the list just once. Right now the list has 23 items and the action in question is to export the results of a query to Excel, using the value from the list as part of the query.

Seems like it should be fairly simple.

However, when I built a small test, and used the Log Comment activity as my action to perform, with the value of the dataset variable as the output, I got the same value 23 times in the log, instead of each unique value. It seems as though it’s not moving through the list and resetting the value for each loop, and I’m sure it has to do with something I’ve failed to configure correctly.

Does anyone have any insight on doing something like this?

2

see this video. http://www.toadworld.com/products/toad-data-point/m/media-library/1133

If that doesn’t solve then attach your automation script and log file and we can take a look

3

Hi Debbie,

That was helpful - thank you! Do you happen to know what the bind variable syntax would be for SQL Server? Would it be the standard @ syntax, or something else? I’ve not used bind variables in my automations to this point.

As it turns out, my issue was that in the Log Comment activity I had only #Loop_data_1_SQL# instead of #Loop_data_1_SQL.<column_name># - Why the first results in only the first value being repeated whereas the second performs as expected is confusing and somewhat counter-intuitive.

4

In Sql Sever use :variableName instead of @variableName in the sql. You need to have variable with the same name (without the : in front) set in the automation script prior to when the sql is run.