email multiple query results to multiple staff

  • Hello all,

    The scene:

    There are 3 people who are all responsible for a number of specific jobs to them.

    I want to email each person at night to tell them which jobs have been logged for them.

    So first I have:

    DECLARE @toemail varchar(150)

    DECLARE @staff varchar(10)

    DECLARE @body varchar(400)

    DECLARE email_cursor CURSOR FOR SELECT 'name@company.com' AS toemail, [Staff Code] AS staff

    FROM vw_jobs

    vw_jobs it a view setup to find jobs entered that day and group on the staff to only return 1 row in an attempt to only find the person I need to email once.

    then I have:

    OPEN email_cursor

    FETCH NEXT FROM email_cursor

    INTO @toemail, @staff

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @toemail

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = @toemail,

    @subject = 'Jobs Logged Today",

    @body_format = 'HTML',

    @body = '

    <html>

    <body>

    Hello @staff

    </body>

    </html>

    ',

    @execute_query_database = 'mydb',

    @query = 'SELECT "<table><tr><td><a href=www.mydomain/jobs.php?staff_code="+ RTRIM([Staff Code]) +"&job_id="+ RTRIM([ID]) +">View Job ID: "+ RTRIM([ID]) +"</a></td></tr></table>"

    FROM jobs

    WHERE ([staff code] = "@staff")'

    FETCH NEXT FROM email_cursor

    INTO @toemail, @staff

    END

    CLOSE email_cursor

    DEALLOCATE email_cursor

    Here are my issues:

    Hello @staff --My @staff variable is not being entered here

    and

    WHERE ([staff code] = "@staff")' --The @staff variable is not being passed here

    if I remove the parameters and hard code:

    OPEN email_cursor

    FETCH NEXT FROM email_cursor

    INTO @toemail, @staff

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @toemail

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = @toemail,

    @subject = 'Jobs Logged Today",

    @body_format = 'HTML',

    @body = '

    <html>

    <body>

    Hello admin_test

    </body>

    </html>

    ',

    @execute_query_database = 'mydb',

    @query = 'SELECT "<table><tr><td><a href=www.mydomain/jobs.php?staff_code="+ RTRIM([Staff Code]) +"&job_id="+ RTRIM([ID]) +">View Job ID: "+ RTRIM([ID]) +"</a></td></tr></table>"

    FROM jobs

    WHERE ([staff code] = "admin_test")'

    FETCH NEXT FROM email_cursor

    INTO @toemail, @staff

    END

    CLOSE email_cursor

    DEALLOCATE email_cursor

    They get an email per job but I want the email to just list the jobs.

    It's still in test so the HTML will be improved on.

    I don't think I'm a million miles away, am I??

    Any help very much appreciated.

    Many Thanks

    WiRL

  • Hello All,

    Right I've fixed this bit:

    Hello @staff --My @staff variable is not being entered here

    by changing to:

    OPEN email_cursor

    FETCH NEXT FROM email_cursor

    INTO @toemail, @staff

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @toemail

    SET @body = '<html><body>Hello ' + @staff + '</body></html>'

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = @toemail,

    @subject = 'Jobs Logged Today",

    @body_format = 'HTML',

    @body = @body,

    @execute_query_database = 'mydb',

    @query = 'SELECT "<table><tr><td><a href=www.mydomain/jobs.php?staff_code="+ RTRIM([Staff Code]) +"&job_id="+ RTRIM([ID]) +">View Job ID: "+ RTRIM([ID]) +"</a></td></tr></table>"

    FROM jobs

    WHERE ([staff code] = "@staff")'

    FETCH NEXT FROM email_cursor

    INTO @toemail, @staff

    END

    CLOSE email_cursor

    DEALLOCATE email_cursor

    But I still can't pass the variable to the query???

    Also How do I get more HTML after the results have been displayed in the email?

    Sorry for all the q's...

    Many Thanks

  • in my case, i don't use the @query parameter; instead i build a varchar max string, and append multiple FOR XML outputs to it.

    I use that string for the @body parameter in sp_send_dbmail.

    i may or may not use a cursor for individual emails, it depends on the target audience; if it was an internal group, i'd be find with everyone's emails in the @recipients string.

    it requires a little more setup as far as the string manipulation for table headers.

    here's a lame example, just building a big varchar of two datasets.

    Declare @HTMLBody varchar(max),

    @TableHead varchar(max),

    @TableTail varchar(max)

    Set NoCount On;

    Set @TableTail = '</table></body></html>';

    Set @TableHead = '<html><head>' +

    '<style>' +

    'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

    '</style>' +

    '</head>' +

    '<body><table cellpadding=0 cellspacing=0 border=0>' +

    '<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' +

    '<td align=center><b>Product</b></td>' +

    '<td align=center><b>Provider</b></td>' +

    '<td align=center><b>Data Source</b></td>' +

    '<td align=center><b>Is Linked?</b></td></tr>';

    --first result set.

    Select @HTMLBody = @TableHead + (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],

    name As

    ,

    product As

    ,

    provider As

    ,

    data_source As

    ,

    is_linked As

    From sys.servers

    Order By is_linked, name

    For XML raw('tr'), Elements) + '</table>'

    --second result set.

    Select @HTMLBody = @HTMLBody + '<table><tr><th>SchemaName</th><th>TableName</th><th>Created Date</th><th>ModifiedDate</th></tr>'

    Select @HTMLBody = @HTMLBody + ISNULL((Select Row_Number() Over(Order By name) % 2 As [TRRow],

    SCHEMA_NAME(schema_id) As

    ,

    name As

    ,

    create_date As

    ,

    modify_date As

    From sys.objects

    WHERE create_date > DATEADD(dd,-7,getdate())

    OR modify_date > DATEADD(dd,-7,getdate())

    Order By modify_date,name

    For XML raw('tr'), Elements),'<tr><td colspan="4">No New Or Modified Objects</td></tr>') + '</table>'

    -- Replace the entity codes and row numbers

    Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))

    Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')

    Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

    Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')

    Select @HTMLBody = @HTMLBody + @TableTail

    -- return output

    Select @HTMLBody

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I like it!

    Will try and slot my info in now..

    Thanks Lowell.

  • Hello Lowell,

    Thanks for the help. I have managed to get it working perfectly now.

    Many Thanks

  • WiRL (3/14/2013)


    Hello Lowell,

    Thanks for the help. I have managed to get it working perfectly now.

    Many Thanks

    glad i could give you a push in a different direction!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply