March 13, 2013 at 8:54 am
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
March 13, 2013 at 10:24 am
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
March 13, 2013 at 10:43 am
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
March 13, 2013 at 10:53 am
I like it!
Will try and slot my info in now..
Thanks Lowell.
March 14, 2013 at 4:22 am
Hello Lowell,
Thanks for the help. I have managed to get it working perfectly now.
Many Thanks
March 14, 2013 at 5:36 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply