Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

email multiple query results to multiple staff Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 8:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 24, 2013 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
Posted Wednesday, March 13, 2013 10:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 24, 2013 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
Posted Wednesday, March 13, 2013 10:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 12,952, Visits: 32,476
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
Posted Wednesday, March 13, 2013 10:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 24, 2013 2:07 AM
Points: 11, Visits: 66
I like it!

Will try and slot my info in now..

Thanks Lowell.

Post #1430530
Posted Thursday, March 14, 2013 4:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 24, 2013 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
Posted Thursday, March 14, 2013 5:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 12,952, Visits: 32,476
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 »

Add to briefcase

Permissions Expand / Collapse