• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

SQL Server job to email output using CDOSYS

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
BEGIN
   select @Body = 'Commission Records were found'
   exec sp_send_cdosysmail 'GBOS@gmail.com','ericwharris@gmail.com','Commission notification System',@Body
END
0
EWHTLC
Asked:
EWHTLC
  • 4
  • 2
1 Solution
 
Louis01Commented:
What is it that you want help with?
0
 
EWHTLCAuthor Commented:
Comment from original question

"If the rowcount is > 0 then the email is sent giving users a notification.
This all works fine but what I would like to do now is send the output via the email rather than the rowcount."

So at the moment An email is sent with the email body (@Body) set to ...........
select @Body = 'Commission Records were found'

I would like the body of the email to represent the output from the sql statement. i.e each individual row

Is this possible


0
 
Louis01Commented:
Untested code, but the general idea is there.
DECLARE @CASE_CASE_NO int;
DECLARE @CASE_BROKER varchar(20);
DECLARE @Body varchar(4000);
DECLARE @hr int
 
SET @hr = 0;
SET @Body = '';
 
DECLARE Commission_Cursor CURSOR FOR
	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';
OPEN Commission_Cursor;
FETCH NEXT FROM Commission_Cursor INTO @CASE_CASE_NO, @CASE_BROKER;
WHILE @@FETCH_STATUS = 0
   BEGIN
		SET @hr = @hr + 1;
		
		-- Remember to convert non varchar fields. (Char(9) is a Tab)
		SET @Body = @Body + convert(varchar, @CASE_CASE_NO) + char(9);
		-- (Char(10) is a Linefeed) (Char(10) is a Carriage return)
		SET @Body = @Body + @CASE_BROKER + char(13) + char(10);
 
		FETCH NEXT FROM Commission_Cursor INTO @CASE_CASE_NO, @CASE_BROKER;
   END;
 
IF (@hr > 0)
BEGIN
   exec sp_send_cdosysmail 'GBOS@gmail.com','ericwharris@gmail.com','Commission notification System',@Body;
END
 
CLOSE Commission_Cursor;
DEALLOCATE Commission_Cursor;
 
GO

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
EWHTLCAuthor Commented:
You Are a stelly eyed Apps man.
That was absolutely perfect
Thanks a lot.
0
 
EWHTLCAuthor Commented:
that should be steely
0
 
EWHTLCAuthor Commented:
absolutely brilliant
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now