Hi guys
Is it possible to place the results of an SQL query (using either the ADO Execute SQL action or the MSSQL Execute SQL action) into an Automise variable?
I'm trying to run a simple query that will return excatly one value, but it seems I can only place the result into a file, or the log. Are they the only options?
Thanks
David Cook
Hi David
There is no direct option to output a single value to a variable, however this is quite easy to achieve using an Output Monitor.
I find that this is easiest to do with the ‘ADO Execute SQL’ action because it will only output the actual results to the log without any other information, which makes it easier to parse.
From the Runtime tab of the action go to the Output Monitors section, add a new output monitor. Set the behavior to ‘Save First Match to Variable’ and select the variable that you want to save the value into.
You then need to set the search parameters, if it is only a single value that is being written to the log then this wont be difficult (set the search string to an asterisk and enable the ‘Use Wildcard characters’ option) , if you wish to capture multiple values you may need to use a regular expression to select the intended values.
Regards,
Steve
Also…
Forgot to mention that if you are only returning a single value from your query then you can just go to the Runtime tab of the action -> Logging Properties -> Log Output To Variable.
This is the best (and by far most simple way) to capture data that is being written to the log.
Regards,
Steve
You can also just use the ADO Dataset Iterator to pull a single value, basically it will iterate over the single row, attempt to move to the second non-existant row and break the loop - works just fine for single rows/values