My goal is to email the results of a SQL query if there are any results returned.
I'm looking for the ability to detect the number of rows returned, but I don't see anything. Same thing for the length of the output string.

Any suggestions?

Take a look at the SQL Task and Variables video tutorial. Instead of writing to a file you can use that Variable in email.

http://visualcron.com/Tutorials.aspx 

There is also RowCount Variable function that you can use:

{STRING(RowCount|value)}

---

The tutorial helped, but I can't seem to find the RowCount variable in the variable list. Can you please let me know where I can find that?

The tutorial helped, but I can't seem to find the RowCount variable in the variable list. Can you please let me know where I can find that?

If you open the Variables window, to find the RowCount Varaiable, follow this path:

%Variables%---->Functions---String.RowCount

I would take that to mean you could use this basic format, to replace 'helloworld' with a variable for your output (this example returns '1'):
{STRING(RowCount|helloworld)}

I'm going to use the {TASK(PrevTask,StdOut)} variable that represents the immediatly preceding tasks Standard Output.

For example, and this is my very simple test, I created a job with 2 tasks.

Task1: SQL Query that does "select * FROM dbo.tablename"
Thats it for Task1. Becuase of this, it returns all the rows into the Std Output for Task1

Task2: E-mail task. In the 'Text' of the message I put this:

There are {STRING(RowCount|{TASK(PrevTask,StdOut)})} rows in your SQL Query Results.

So my e-mail said this, which was correct in my case:
There are 1733 rows in your SQL Query Results

Hope this helps,

Brian

Thanks for the reply Brian. I was going about it differently, which may be my problem.

I only have one task in my job that executes the following SQL:



Then I setup a notification within that task to send me the output. I have included {TASK(Active,StdOut)} in the body of the notification, but it's not printing anything out.

Do I need to setup a second task that is an email to make this work this way?

Thanks,

Bob

Thanks for the reply Brian. I was going about it differently, which may be my problem.

I only have one task in my job that executes the following SQL:

USE dbAction

DECLARE @cnt INT

update tblAgent
set TaxID = '999-99-9999'
where agentNumber = 'ttttt'

SET @cnt = @@ROWCOUNT
IF @cnt > 0 PRINT CAST(@cnt AS VARCHAR) + ' rows were updated';
	ELSE 
		PRINT 'No rows were updated';

Then I setup a notification within that task to send me the output. I have included {TASK(Active,StdOut)} in the body of the notification, but it's not printing anything out.

Do I need to setup a second task that is an email to make this work this way?

Thanks,

Bob

When you're running the task by itself, regardless of the e-mail, do you see your results in the 'Output' of the task within the client?

I still have my test Job I created for the reply I sent, so I just deleted the e-mail (2nd one) task and am now left with just the one SQL Query task.
I then edited the task, went to the 'Notifications' tab, and added a new Email notification task. I checked the box for 'Notify on Complete' and did as you said, putting the following in the 'Message/Body' tab of the notification:
{TASK(Active,StdOut)}

I got 1733 lines of results in my e-mail :)

Brian

I have been playing around with this some more since my last post. It appears that it all depends on the SQL. If I change my SQL to a simple select, I get the output as expected. However, in my SQL I am doing an update, and that doesn't seem to be sending an output.

If you change your SQL to a simple update of one row, do you still get that in the email?

I have been playing around with this some more since my last post. It appears that it all depends on the SQL. If I change my SQL to a simple select, I get the output as expected. However, in my SQL I am doing an update, and that doesn't seem to be sending an output.

If you change your SQL to a simple update of one row, do you still get that in the email?

You are correct. I just did a one line SQL query updating the notes field in one of our database fields and I did not get any output.

However, and i'm not strong in SQL btw, I would've thought 'printing' something like you did would've written something to the screen and would've been deemed 'output'.

Brian

Me too. Which is why I wrote the explicit print statement within the query. I'm confused as to why it's not showing up.

Can I get some help from an Admin on this please?

Me too. Which is why I wrote the explicit print statement within the query. I'm confused as to why it's not showing up.

Can I get some help from an Admin on this please?

If you have the option, remove the [Solved] piece from your original post (edit it). That way Heinrik sees it for sure.

Brian

USE dbAction

DECLARE @cnt INT

update tblAgent
set TaxID = '999-99-9999'
where agentNumber = 'ttttt'

SET @cnt = @@ROWCOUNT
IF @cnt > 0 PRINT CAST(@cnt AS VARCHAR) + ' rows were updated';
	ELSE 
		PRINT 'No rows were updated';

Even when I run the above SQL in the SQL explorer within Connections, the print statement doesn't appear in the output. I don't understand why.

Hi,

PRINT is not outputted. This information is not forwarded to our SQL client. This is something that is done by design by Microsoft. Maybe there is another command that you can use. We are still investing if it is.

---

Hi,

PRINT is not outputted. This information is not forwarded to our SQL client. This is something that is done by design by Microsoft. Maybe there is another command that you can use. We are still investing if it is.

This is unfortunate. This makes Visual Cron unusable for me for hundreds of automated tasks. What do you mean by done by design by Microsoft? When I run this query in SQL Analyzer and SQL Studio it prints the output.

Hi,

PRINT is not outputted. This information is not forwarded to our SQL client. This is something that is done by design by Microsoft. Maybe there is another command that you can use. We are still investing if it is.

This is unfortunate. This makes Visual Cron unusable for me for hundreds of automated tasks. What do you mean by done by design by Microsoft? When I run this query in SQL Analyzer and SQL Studio it prints the output.

We are still investigating this. What we have found so far is that PRINT is not part of result of query any way. It is part of debug messages that can optionally be picked up using other events. The problem with that approach is that it may not match normal output. For example, if you return a query with 5 columns and 10 rows. Then the question is which column and row should the debug info PRINT be on? That is why they have separated this.

But, we are still investigating.

---

Hi,

PRINT is not outputted. This information is not forwarded to our SQL client. This is something that is done by design by Microsoft. Maybe there is another command that you can use. We are still investing if it is.

This is unfortunate. This makes Visual Cron unusable for me for hundreds of automated tasks. What do you mean by done by design by Microsoft? When I run this query in SQL Analyzer and SQL Studio it prints the output.

We are still investigating this. What we have found so far is that PRINT is not part of result of query any way. It is part of debug messages that can optionally be picked up using other events. The problem with that approach is that it may not match normal output. For example, if you return a query with 5 columns and 10 rows. Then the question is which column and row should the debug info PRINT be on? That is why they have separated this.

But, we are still investigating.

Thank you. I appreciate you looking into this. I will look into some other alternatives as well.

Ok, we found a workaround:

Instead of using PRINT you just use SELECT instead:

SELECT 'No rows were updated'

---

Ok, we found a workaround:

Instead of using PRINT you just use SELECT instead:

SELECT 'No rows were updated'

EDIT: Ignore this. I didn't see your other responses above your workaround. I see you're still looking at this:
/EDIT

That does work.

Since you mentioned the word 'workaround' does that mean there is something that needs to be fixed at some point or is this somehow a limit of the sql language and 'Update' does not send output like Select does?

Brian

Ok, we found a workaround:

Instead of using PRINT you just use SELECT instead:

SELECT 'No rows were updated'

THANK YOU! I can make this work now with a few tweaks. Much appreciated.

Ok, we found a workaround:

Instead of using PRINT you just use SELECT instead:

SELECT 'No rows were updated'

EDIT: Ignore this. I didn't see your other responses above your workaround. I see you're still looking at this:
/EDIT

That does work.

Since you mentioned the word 'workaround' does that mean there is something that needs to be fixed at some point or is this somehow a limit of the sql language and 'Update' does not send output like Select does?

Brian

I see this as the correct way to do this but we might pick up the debug messages in the future as well. But before doing this we need to prepare for changes needed for the SQL Trigger we will develop soon.

---