SQL Server job to email output using CDOSYS
Posted on 2008-11-04
I currently have a sql server job which runs a step which runs a prog to set a rowcount.
If the rowcount id > 0 then the email is sent giving users a notification.
This all works fine but what I would like to do now id send the output via the email rather than the rowcount.
Here's my job.
Declare @hr int
Declare @Body varchar(4000)
--put the sql statement here
SELECT CASE_CASE_NO, CASE_BROKER
FROM dbo.tbl_Case LEFT OUTER JOIN
dbo.tbl_Broker ON dbo.tbl_Case.CASE_BROKER = dbo.tbl_Broker.BROKER_CODE
WHERE (CASE_STATUS = 'Completed') AND
((CASE_BKR_COMM_PAID_DATE = '00000000' or CASE_BKR_COMM_PAID_DATE = '')) and
dbo.tbl_Broker.BROKER_ACTIVE = 'Y' and CASE_BROKER <> 'GHF'
--set up the error handler
SET @hr= @@rowcount
-- check for error
IF @hr <> 0
select @Body = 'Commission Records were found'
exec sp_send_cdosysmail 'GBOS@gmail.com','email@example.com','Commission notification System',@Body