Solved

SQL and DBMail to send query output

Posted on 2013-05-31
25
1,198 Views
Last Modified: 2014-05-30
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
0
Comment
Question by:mig1980
  • 10
  • 8
  • 3
  • +1
25 Comments
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39211850
What parts do you need help with?
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 39211866
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
 
LVL 39

Expert Comment

by:lcohan
ID: 39211867
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39211868
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
 

Author Comment

by:mig1980
ID: 39211918
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
 
LVL 26

Expert Comment

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

Author Comment

by:mig1980
ID: 39217087
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39217159
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
 

Author Comment

by:mig1980
ID: 39217174
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39217194
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
 

Author Comment

by:mig1980
ID: 39227146
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 26

Expert Comment

by:Zberteoc
ID: 39227163
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
 

Author Comment

by:mig1980
ID: 39227496
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39229353
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39229380
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
 

Author Comment

by:mig1980
ID: 39239050
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39239252
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39239261
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
 
LVL 26

Accepted Solution

by:
Zberteoc earned 250 total points
ID: 39239296
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
 

Author Comment

by:mig1980
ID: 39282500
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39576349
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
 

Author Comment

by:mig1980
ID: 40098639
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now