SQL and DBMail to send query output

What parts do you need help with?

For more please see my answers at:

https://www.experts-exchange.com/questions/26826472/sp-send-dbmail-format.html?sfQueryTermInfo=1+10+30+dbmail+send+sp

--colored HTML
https://www.experts-exchange.com/questions/26963149/SQL-e-mail-loop-through-returned-records.html

Sample code below:


To send query output as attachemnt:

eclare @report_file_name varchar(30),
            @sql varchar(max),
            @emailbody varchar(4000)
      
      set @sql = N'SET NOCOUNT ON
                        SELECT whatever FROM whatever_table WHERE whatever'

      SET @report_file_name = 'file_list_at_' + convert(varchar(10),getdate(), 112) + '.txt'
      SET @emailbody = 'Here is the list you wanted as today(yyyy.mm.dd):  '+ convert(varchar(10),getdate(), 102)
      EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Mail Profile', -- mail profile here
            @recipients='user@mail.com',
            @subject = 'This is a TEST email',
            @body = @emailbody,
            @body_format = 'TEXT',
            @query = @sql,
            @attach_query_result_as_file = 1,
            @execute_query_database = 'YourDB', -- your database name here
            @query_attachment_filename = @report_file_name,
            @query_result_header = 1,
            @query_result_no_padding = 1,
            @query_result_separator = '      ',
            @query_result_width = 1000;

To send attachmet only:

      EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Mail Profile', -- mail profile here
            @recipients='user@mail.com',
            @subject = 'Your image file has arrived...',
            @body = 'Here is your image file - see attached',
            @body_format = 'TEXT',
            @file_attachments = 'E:\PicturesFolder\Pic1.jpg';

OK, I see some interesting information here. Here the steps I was looking for. They may already be above but some of the pseudo code doesn't make sense.

1) Capture file name, date, time, size, area from view
2) Create a file containing this information
3) Pull the email address and CC associated with the area from the View as well and use them to deliver the email.
4) Create an email with this file attached and some information in the body and subject for the first "area" (If it contains rows pertaining to that area, include them in the file...if not, send out an email explaining that no information was available) and loop for all 32 areas.

I mainly need help with how to loop until all 32 areas receive emails but would appreciate help with all above. One more note, there are 32 total areas but there is no area 25. The areas go from 1-24 and 26-33.

Let me know if more information is needed.

I don't see any email information in the sample data. How do you know what area info to which email to send?

I did not include the email columns in the sample data but there would be two email columns. One where the To email would go and another where the CC email would go.

There has to be a table with all the area codes available. If yes you will have to use it to left join the view to it.

There is no specific table with all areas per say. Could that not be hardcoded? Also, could someone provide a sample loop for this to work?

Here it is:

declare 
	@report_file_name varchar(30),
	@sql varchar(max),
	@emailbody varchar(4000),
	@area varchar(5)
	
-- create a "temporary" permanent table to store all the data; you can't use an actual temp table in this case
if object_id('tmp_email') in not null
	drop table tmp_email
select 
	[File Name], [Date], [Time], [File Size (Kb)], to_email, cc_email 
into tmp_email 
from 
	areas a
	left join your_view v
		on a.area=v.area
order by 
	a.Area

-- get the first area	
select top 1 
	@area=Area,
	@to_email=to_email,
	@cc_email=cc_emai
from 
	tmp_email
-- dont insert anything here; loop through areas
while @@ROWCOUNT>0 -- where thers is nothing left will stop
begin  
set @sql = N'SET NOCOUNT ON
                        SELECT * from tmp_email where Area='+@area

	SET @report_file_name = 'file_list_at_' + convert(varchar(10),getdate(), 112) + '.txt'
	SET @emailbody = 'Here is the list you wanted as today(yyyy.mm.dd):  '+ convert(varchar(10),getdate(), 102)
	EXEC msdb.dbo.sp_send_dbmail 
		@profile_name = 'Database Mail', -- mail profile here
		@recipients=@to_email,
		@copy_recipients=@cc_email
		@subject = 'Files Email',
		@body = @emailbody,
		@body_format = 'text',
		@query = @sql,
		@attach_query_result_as_file = 1,
		@execute_query_database = 'zb_dba_maint', -- your database name here
		@query_attachment_filename = @report_file_name,
		@query_result_header = 1,
		@query_result_no_padding = 1,
		@query_result_separator = '|',
		@query_result_width = 1000;
	-- remove the rows for current area
	delete from tmp_email where Area=@area;
	--get the next area
	select top 1 
		@area=Area,
		@to_email=to_email,
		@cc_email=cc_emai
	from 
		tmp_email
-- don't insert anything here
end
-- drop the tm- table
drop table tmp_email

Select allOpen in new window

Say I were able to pull the unique areas in the following way:

select distinct area from RDB

Select allOpen in new window

How would this change the query above?

Then the only thing needed is to replace:

...
from 
	areas a
	left join your_view v
...

Select allOpen in new window

with

...
from 
	(select distinct area from RDB) a
	left join your_view v
...

Select allOpen in new window

Thank you for all of your help thus far. I have another dilemma. I want to have the a different email address per area. No CC. Just one person receiving each area email.

My problem is that we have an accounts table which houses all data for each account including email and a field labeled Primary. That means that this person is the primary contact for the area.

How can I write the query to pull the email of the primary for the area?

You will have to join that table in the query posted above and get the contact email based on area. Also you could alter the view to actually join that table and return the email for contact person, in which case you will only need to modify the columns list to get that email. This is really elementary SQL now we're talking.

Here is the version joining the contact table. You add the tabel in the join and select teh contact_email from it to assign it to the @to_email variaobe. Just comment the @cc_email lines:

declare 
	@report_file_name varchar(30),
	@sql varchar(max),
	@emailbody varchar(4000),
	@area varchar(5)
	
-- create a "temporary" permanent table to store all the data; you can't use an actual temp table in this case
if object_id('tmp_email') in not null
	drop table tmp_email
select 
	[File Name], [Date], [Time], [File Size (Kb)], to_email, cc_email, c,contact_email 
into tmp_email 
from 
(select distinct area from RDB) a
	left join your_view v
		on a.area=v.area
	left join area_contacts c
		on c.area=a.are
order by 
	a.Area

-- get the first area	
select top 1 
	@area=Area,
	@to_email=contact_email
	--,@cc_email=cc_emai
from 
	tmp_email
-- dont insert anything here; loop through areas
while @@ROWCOUNT>0 -- where thers is nothing left will stop
begin  
set @sql = N'SET NOCOUNT ON
                        SELECT * from tmp_email where Area='+@area

	SET @report_file_name = 'file_list_at_' + convert(varchar(10),getdate(), 112) + '.txt'
	SET @emailbody = 'Here is the list you wanted as today(yyyy.mm.dd):  '+ convert(varchar(10),getdate(), 102)
	EXEC msdb.dbo.sp_send_dbmail 
		@profile_name = 'Database Mail', -- mail profile here
		@recipients=@to_email,
		--@copy_recipients=@cc_email
		@subject = 'Files Email',
		@body = @emailbody,
		@body_format = 'text',
		@query = @sql,
		@attach_query_result_as_file = 1,
		@execute_query_database = 'zb_dba_maint', -- your database name here
		@query_attachment_filename = @report_file_name,
		@query_result_header = 1,
		@query_result_no_padding = 1,
		@query_result_separator = '|',
		@query_result_width = 1000;
	-- remove the rows for current area
	delete from tmp_email where Area=@area;
	--get the next area
	select top 1 
		@area=Area,
		@to_email=contact_email
		--,@cc_email=cc_emai
	from 
		tmp_email
-- don't insert anything here
end
-- drop the tm- table
drop table tmp_email

Select allOpen in new window

So I have been working on this and I think it is all good (still need to test with data) but I have one question.

I am trying to figure out the following:

If the job reaches an area that does not have any data for the month, I want it to send an email anyway but in the body of the email state that there are is no data for the month and not attach a file.

The current select statement to populate the tmp table pulls data that exists and also creates a row with area number for those areas that have no records.

That is exactly the point and that is why I used LEFT JOIN in

into tmp_email
from
(select distinct area from RDB) a
      left join your_view v
            on a.area=v.area
      left join area_contacts c
            on c.area=a.are

which makes sure all the areas from (select distinct area from RDB) will be selected and if they have any corresponding rows in your_view and area_contacts then the columns will have values otherwise will be NULL.

In the Loop the select statement for areas with no rows will return nothing so the email basically will be empty, maybe only column headers.

Sorry, actually the area code will be returned but the rest of the columns will be NULL/empty. If you want you could check that and generate a specific email for that situations and not just empty columns

I plan to review this and test fully in the next few weeks. Will return to assign point or ask more questions.

Thank you

Are you kidding? I did all the heavy lifting and you gave the points to the first answer? Did you forget about it an now you just closed it?

I agree with Zberteoc that more points should go to him as the ultimate design for the final script I created contained more details from his/her assistance than from lcohan, but I did use information form both as well as other details from my personal knowledge. It was a mistake on my part when closing the question.

I also did not give the points to the previous expert as mentioned by Zberteoc. I split them up evenly as I felt that was the case initially. After reviewing my process, below I state the actual outcome.

I would like to add that what I ended up doing was leveraging both scripts (mostly Zberteoc's) to fit my needs and added the script into a job within SQL that I have running once a month.

If an admin can assign 400 points to Zberteoc and 100 to lcohan, that would be great.