1

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:

enter image description here

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.

  • Can you add table definitions ans what you currently have? – Tom V Feb 5 '16 at 12:50
1

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]
    

    enter image description here

  • 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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.