SQL and DBMail to send query output

Good day everyone. I am attempting to construct a SQL Job that pulls a set of information included below.

I then want to email this information the specific email addresses associated with the "area" number. I would be fine emailing it as a txt file but would prefer a csv file.

Each email would contain a file attached which would contain 1 or more rows depending on how many rows are listed for that "area" in the information pulled from the View listed below.

If there is an area that has no rows listed, send an email but state in the body of the email that no files are listed.

The sample data attached contains information to send out for area 1, 3, 6, 9, 26. This information is pulled directly from a View in one of our DBs. I want emails to go out to the remainder of the 32 areas stating in the body that there is no data to send them for the month.

NOTE: The email addresses these emails will go to are already known. They will be pulled from the View as well.
SampleData-byarea.xlsx
mig1980Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ZberteocConnect With a Mentor Commented:
This would be the script:
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 DISTINCT * from tmp_email where Area='+@area
	
	IF exists (select * FROM tmp_email where Area=@area and [File Name] is not NULL)
	begin
		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;
	end
	else
	begin
		SET @emailbody = 'There is not data for today:  '+ 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'
		
	end
	-- 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

Open in new window

0
 
didnthaveanameCommented:
What parts do you need help with?
0
 
lcohanConnect With a Mentor Database AnalystCommented:
If you want the fancy colored HTML you can use the pseudo code below to create your own:

create table #test (column1 int, column2 varchar(max), column3 varchar(max), column4 varchar(max))
insert into #test SELECT 1 ,'text2','text3','text4'
insert into #test SELECT 2 ,'code2','code3','code4'


DECLARE @title nvarchar(200)
DECLARE @tableHTML nvarchar(MAX)
SET @tableHTML =
N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:12px; border-collapse:collapse;}
td{background-color:#F1F1F1; border:1px solid black; padding:4px;} th{background-color:#99CCFF; padding:4px;}
h4{font-family: "Courier New", Courier, monospace; font-size: 11px;} </style>'


SET @title = 'Summary Report - FunctionCode'
SET @tableHTML = @tableHTML +
    N'<H1>' + @title + '</H1>' +
    N'<table border="1">' +
    N'<tr style="color:green;background-color:purple;">' +
      N'<th>Column1</th>' +
      N'<th>Column2</th>' +
      N'<th>Column3</th>' +
      N'<th>Column4</th>' +
     CAST(
            (SELECT
                  td = Column1 ,'',
                  td = Column2,'',
                  td = Column3,'',
                  td = Column4,''
            FROM  #test WHERE column1 in (1,2)
            FOR XML PATH('tr'), TYPE)
       AS NVARCHAR(MAX) )
       + N'</table>' ;

IF @tableHTML is not null
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'mail_profile',
            @recipients='email@email.com',
            @subject = @title,
            @body = @tableHTML ,
            @body_format = 'HTML' ;
END

drop table #test
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
lcohanDatabase AnalystCommented:
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';
0
 
mig1980Author Commented:
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.
0
 
ZberteocCommented:
I don't see any email information in the sample data. How do you know what area info to which email to send?
0
 
mig1980Author Commented:
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.
0
 
ZberteocCommented:
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.
0
 
mig1980Author Commented:
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?
0
 
ZberteocCommented:
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

Open in new window

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

select distinct area from RDB

Open in new window


How would this change the query above?
0
 
ZberteocCommented:
Then the only thing needed is to replace:

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

Open in new window

with

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

Open in new window

0
 
mig1980Author Commented:
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?
0
 
ZberteocCommented:
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.
0
 
ZberteocCommented:
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

Open in new window

0
 
mig1980Author Commented:
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.
0
 
ZberteocCommented:
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.
0
 
ZberteocCommented:
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
0
 
mig1980Author Commented:
I plan to review this and test fully in the next few weeks. Will return to assign point or ask more questions.

Thank you
0
 
ZberteocCommented:
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?
0
 
mig1980Author Commented:
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.
0
All Courses

From novice to tech pro — start learning today.