Emailing directly from job

[Ofina]

I'm setting up an SQL job that I want to email me.

Let's say step one is to run
select * from orders

Can step 2 be to email me the results of that select statement? Failing that, can I put the code to do this into the sql of step 1?

I'd like to avoid a stored procedure if I can.

SQL 2008

 

[RTag]

you can use xp_sendmail and to provide the query.

http://msdn.microsoft.com/en-us/library/ms189505.aspx

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[philhege]

http://msdn.microsoft.com/en-us/library/ms190307.aspx

xp_sendmail is an extended SP that's headed to the bit bucket.

-------++NO CARRIER++-------

 

[SQLBill]

For SQL 2008 use sp_send_dbmail.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[RTag]

Sorry, I agree sp_send_dbmail is better for sql2008.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[Ofina]

I can put that right in the job? Same step or new step?

I know about sp_send_dbmail, but I've only ever used it in a stored procedure.

 

[SQLBill]

Yes, you can put it in a job step. Either way that works for you - the same step or a new one.

Very basically, any script that you can run in a query window you can also put into a job step.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Ofina]

Okay, this is working now. But, now I want to not email anything out if the result of the select statement is empty.

 

[SQLBill]

Try using an IF statement,something like...

IF (SELECT COUNT() .....) > 0

<send email>

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[cantor001]

One alternative to using count() would be

if exists(select null .....)

I'm sure there are others.

 

[Ofina]

That's actually the one I wound up using today. The initial suggestion errored out.