I have a table which has a list of ticket owners and and tickets which are assigned to them which are of high priority.
For example:
I need to send mail from SQL Server using sp_send_dbmail to John with all the tickets assigned to him in a single mail. This is a huge table so I want send mails to users with their assigned tickets in single mail instead of sending one mail per ticket. Any help would be appreciated.
Below is how you do it ...
Create table and add processed column
create table dbo.Tickets (Ticket bigint, [Owner] varchar(50))
go
insert into dbo.Tickets
select 14675, 'John'
union all
select 38759, 'John'
union all
select 45879,'Catheline'
union all
select 23980, 'Mark'
union all
select 16754, 'John'
--- add a processed column so that next time the db mail job runs, it wont send out dupe emails
alter table dbo.Tickets add processed bit not null default(0)
go
Use below query
-- query to get comma seperated values
select [Owner], Tickets = stuff ((select N', '+ convert(nvarchar(max),innerT.Ticket)
from dbo.Tickets innerT
where innerT.[Owner] = outerT.[Owner]
group by innerT.Ticket -- get rid of dupes
order by innerT.Ticket -- just in case you want order
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
from dbo.Tickets outerT
group by [Owner]
Use above query to send HTML email using database mail.
Update the dbo.Tickets table and set processed = 1 for the owners that you emailed the tickets.
Note: you can use case statements based on the owners and email them seperately.
This is a pseudo code that will help you 80% in what you want to achieve.
The way I understand your question is that you would like to send 1 mail to each person with each a list of tickets they have assigned.
For convenience I have created a temporary table with temporary data based upon your information. The email column was missing in yours.
I create first a cursor that select distinct the owners.
When I run over the cursor of owners one by one I can prepare the html email header part and the html table that will hold the list of tickets.
Then I will create another cursor, this time select all tickets where the owner is the one we are currently working on. Running over this cursor allows me to add the tickets are rows to created html table.
When finished with this owner, I close the html table and the html email. At this point you can send out the mail to the owner with the list of tickets.
Here is the full script:
-- Create temporary table
CREATE TABLE [dbo].[zz_test]
(
[Ticket] int NOT NULL,
[Owner] varchar(50) NOT NULL,
[Email] varchar(50) NOT NULL
)
-- Insert dummy data
INSERT INTO [dbo].[zz_test] ([Ticket], [Owner], [Email])
VALUES
(14675, 'John', 'john@company.org'),
(38759, 'John', 'john@company.org'),
(45879, 'Catheline', 'Catheline@company.org'),
(23980, 'Mark', 'Mark@company.org'),
(16754, 'John', 'john@company.org')
-- Start procedure
DECLARE @Owner varchar(50)
,@Email varchar(50)
,@Ticket int
,@HTML nvarchar(max);
-- Create cursor with owners
DECLARE curOwners CURSOR FAST_FORWARD
FOR
SELECT DISTINCT [Owner], [Email] FROM [dbo].[zz_test];
-- Open cursor and run over them one by one
OPEN curOwners;
FETCH NEXT FROM curOwners INTO @Owner, @Email;
WHILE @@FETCH_STATUS = 0 BEGIN
-- Create html email header
SELECT @HTML = N'<!DOCTYPE html>
<html>
<head lang="en">
<title></title>
<style type="text/css">
html {
color: #222;
font-size: 1em;
-webkit-font-smoothing: antialiased;
}
body {
padding: 0;
margin: 0;
width: 100%;
}
#main {
font-family: "Segoe UI", sans-serif;
font-size: 12px;
margin: 0 auto 100px auto;
position: relative;
font-weight: 300;
}
h1 {
font-family: "Segoe UI", sans-serif;
font-weight: 500;
font-size: 18px;
margin-bottom: 12px;
}
h2 {
font-family: "Segoe UI", sans-serif;
font-weight: 500;
font-size: 16px;
}
table {
border-collapse: collapse;
border-spacing: 0;
padding: 0;
margin: 0;
font-family: "Segoe UI", sans-serif;
font-size: 12px;
}
caption { text-align: left; padding: 2px;}
thead tr th { background-color:#ddd; }
th {
background-color: #eee;
border: 1px solid #ccc;
color: #555;
text-align: center;
font-weight: 700;
padding: 1px 4px;
font-family: "Segoe UI", sans-serif;
font-size: 12px;
}
td {
border: 1px solid #ccc;
vertical-align: middle;
padding: 1px 4px;
font-family: "Segoe UI", sans-serif;
font-size: 12px;
}
th.fix60 { width: 60px; }
th.fix90 { width: 90px; }
th.fix120 { width: 120px; }
th.fix140 { width: 140px; }
th.fix180 { width: 180px }
.container table {
border: 1px solid #e7e7e7;
margin: 0 -1px 24px 0;
text-align: left;
width: 100%;
}
.container tr { background-color:#fff; }
</style>
</head>
<body>
<div id="main">';
SELECT @HTML = @HTML + N'<p style="font-weight:bold">List of tickets for ' + @Owner + '.</p>';
-- Create table header
SELECT @HTML = @HTML + N'<table><thead><th class="fix140">Ticket</th></thead><tbody>';
-- Create cursor with tickets of the owner we are working on
DECLARE curTickets CURSOR FAST_FORWARD
FOR
SELECT DISTINCT [Ticket] FROM [dbo].[zz_test] WHERE [Owner] = @Owner;
-- Open the cursor and run over the tickets one by one and add them to a table row
OPEN curTickets;
FETCH NEXT FROM curTickets INTO @Ticket;
WHILE @@FETCH_STATUS = 0 BEGIN
-- Add row for every ticket
SELECT @HTML = @HTML + N'<tr><td>' + CAST(@Ticket as varchar(50)) + N'</td></tr>';
FETCH NEXT FROM curTickets INTO @Ticket;
END;
CLOSE curTickets;
DEALLOCATE curTickets;
-- Close table
SELECT @HTML = @HTML + N'</tbody></table>';
-- Close html
SELECT @HTML = @HTML + N'</body></html>';
-- Print html
SELECT @HTML;
-- Send mail
/*EXEC msdb.dbo.sp_send_dbmail
@profile_name='profil name',
@recipients=@Email,
@subject = 'Subject... list of tickets',
@body = @HTML,
@body_format = 'HTML',
@importance= 'high';*/
FETCH NEXT FROM curOwners INTO @Owner, @Email;
END;
CLOSE curOwners;
DEALLOCATE curOwners;
-- Drop temporary table
DROP TABLE [dbo].[zz_test];
Note: In the full script I have disabled the send email. Currently there is a Select in place to show you the generated html. Just copy the content to a notepad and save it as html to see how it looks. It might be that you need to adjust the css at your preference.
