I want to setup a number of tasks which will be used to audit bits of my SQL system, however I only want to send an email if there was some data returned as a reuslt of running the query.

Is this possible ?

Sure,

Use a job with two tasks. One is the SQL task and the other is the email task.
The email task has a condition set to check if the output of the previous task isn't empty.
So the email task will only run if there is output.

Regards
Erik

---

Hi Erik,

Thanks for reply - I haven't used conditions prior, but how do you set it to check for not blank ?

If you change the "Output" to "Is Not" you must enter something in box.

(Sorry was trying to put a screen shot in here but that doesn't appear to work !)

Thanks

Graham

What you can do is creating an empty string variable.
Than you can use this variable in the Output Is Not condition.

I tested this and it worked.

Regards

---

Hi Erik,

I am having problem with this SQL task and getting it to work if empty.

If I use a dummy sql command i,e select 'abc' and use that for the checking in conditions, i.e. when output is 'abc', then I can get conditions to work.

If I use real sql, i.e. select * from table name where name like 'aaaaaa%' which will return no records, I can't get the conditions to fire correctly. I always seem to get the email.

The user variable is set as string with nothing entered in the box. The condition box contains {USERVAR(EmptyString)}

Thanks in advance for any help you can provide.

Graham

I guess there is some kind of difference, like a new line or similar. If you open the output - is it really empty (no blank space / no return)?

---

Hi Support,

In the email response, I entered, including quotes "a{TASK(4ab616d5-7a4a-4721-aad8-404d7b42041f,StdOut)}" and the only thing return back in the response email was "a" therefore indicating the return is indeed blank. However, is it possible a difference between Null or blank on a SQL return and how VC see's it ? Erik said he had it working so it's possible I have done something wrong.

Would it be possible to add something to the SQL task which will catch a Null/blank return and generate an error or something similar.

I did go through the enhanced debugging as per your suggestion, but this didn't make sense in the same way the the 'abc' test did.

Thanks

Graham

Yes, or we could just add support for entering an empty value in output (because I guess it is not possible as you use a Variable)?

---

I only used the variable at Eriks suggestion - it seemed a good idea to me, but alas I can't make it work.

If I can make this work, that's fine by me - the problem goes away, but there are always simpler(alternative) ways to resolve a problem.

I think I made a mistake. I cannot reproduce the working version anymore. It was a good idea however.. With the support of empty values in the field, this problem is solved (I think).

---

Were any changes made to VC to support checking on Null/empty return from SQL task or is there another method of achieving this ?


THanks in advanve.


Graham

A question. Is the problem only with null values? What if you make a simple select on an Id in a table that does not exist - does the comparison fail then? If so, what database type and version do you use?

---

The question is how do I actually test for nothing being returned ?

What I am trying to do is only send an email if something is returned from the SQL query, i.e. if "Select * from table where fieldname = 'XXXX' " returns nothing (0 lines) how do I then test against this to determine the next task action.

The output field on the SQL task shows "No output" but there appears to be no way to test on "empty" output, is it Null or "" or something else.

Possible solutions to this would be

a) The Output variable is set to Null if there isn't any and then allow a condition to be test it.
b) A new SQL output variable is set which is Y/N to indicate whether any output was generated.
c) use the Error tab, add a "Null output" condition which in turn would allow the job/task to end or whatever.

Graham

We will review this once we start working on the more advanced comparing in Conditions. Thank you.

---

Not sure why, but this does now work following further testing. When you set the condition you just leave it blank when checking for "Output". The system asks for confirmation which you accept.

The next task, in my case email, will only run when output is returned from the SQL output.

I would paste a screenshot but can't make it work.


Graham