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
Eric HarrisDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Louis01Commented:
What is it that you want help with?
0
Eric HarrisDeveloperAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Eric HarrisDeveloperAuthor Commented:
You Are a stelly eyed Apps man.
That was absolutely perfect
Thanks a lot.
0
Eric HarrisDeveloperAuthor Commented:
that should be steely
0
Eric HarrisDeveloperAuthor Commented:
absolutely brilliant
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Email Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.