email multiple query results to multiple staff

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » email multiple query results to multiple...

email multiple query results to multiple staff

Rate Topic

Display Mode

Topic Options

Author

Message

WiRL

Posted Wednesday, March 13, 2013 8:54 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:07 AM
Points: 11, Visits: 66

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

Post #1430443

WiRL

Posted Wednesday, March 13, 2013 10:24 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:07 AM
Points: 11, Visits: 66

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

Post #1430510

Lowell

Posted Wednesday, March 13, 2013 10:43 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 11,648, Visits: 27,768

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 [TD],
            product As [TD],
            provider As [TD],
            data_source As [TD align=center],
            is_linked As [TD align=center]
      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 [TD],
            name As [TD],
            create_date As [TD],
            modify_date As [TD]
      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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1430524

WiRL

Posted Wednesday, March 13, 2013 10:53 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:07 AM
Points: 11, Visits: 66

I like it!

Will try and slot my info in now..

Thanks Lowell.

Post #1430530

WiRL

Posted Thursday, March 14, 2013 4:22 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:07 AM
Points: 11, Visits: 66

Hello Lowell,

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

Many Thanks

Post #1430861

Lowell

Posted Thursday, March 14, 2013 5:36 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 11,648, Visits: 27,768

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1430896

« Prev Topic | Next Topic »

Permissions