October 13, 2011 at 10:30 pm
Hi
Today, I was working on creating a disk space monitoring utility in one of our test environment so that when a specific session is running over the limit in space in temp DB, an alert email is sent to the DBA about the issue with the appropriate information of the session ID and Login information and other metadata information.
I can retrieve the login ID for the session but what if i would also like to send an warning email to the login ID?
A login ID such as an employee ID can be easily resolved if using Outlook for sending an email but can DB Mail do the autoresolution of the related email address from the employee ID when sending an email?
I am not sure if i framed my question correctly but if anyone has any thoughts on this, it would be much appreciated.
Thanks
October 14, 2011 at 1:51 am
As far as I know, there is no built-in functionality for this. What Outlook does, is querying Active Directory for information. You could of course create a .NET CLR Stored Procedure or Function doing the same if you really need this functionality.
October 14, 2011 at 9:11 am
I agree.
This feature would be best in use if you are addressing email groups in the recipients list to be auto-resolved at send time rather have to explicitly mention the complete email address.
How about having the group in the agent operators list, can that be used as a workaround?
I know in SSIS, Notify operator task does this easily but from within the database engine, can the above workaround be used?
October 14, 2011 at 10:47 am
It's not a workaround, it's best practice. Except during development, you should never configure applications, stored procedures, or anything else to send mail automatically to individual users, but use groups instead. Even if the mail shall only have one recipient. It requires less effort to modify one or a few groups, than alter a lot of applications, stored procedures, SSIS packages etc. Whether you should use a security group, distribution group or query based distribution group would depent on your requirements.
October 15, 2011 at 9:26 pm
If we assumed that the login IDis the same one like Mail ID within a specific mail domain , we could up with the below query
to approch the business target..:
Declare @loginid varchar (50)
Declare @MailTo varchar (50)
Declare @MailSubject varchar (100)
Declare @MailBody varchar (1000)
select @loginid='person_name'
Set @MailTo=@loginid+'@domain.com'
EXEC msdb.dbo.sp_send_dbmail ,
@recipients=@MailTo,
@subject=@MailSubject,
@body=@MailBody ,
@body_format='HTML'
Print'a Notification Email has been sent to '+@mailto+' '
Please confirm if it fits with your business requirements
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
December 1, 2011 at 5:54 pm
My Question to performance guard::
Don't you think this is somewhat similar to hardcoding the content.
You are hardcoding the domain for one reason where it cannot always be assumed that emails will not be sent outside the specified domain in which case the send email attempt would fail if domain name is hardcoded.
I understand that database mail has been completely made independent of an exchange server and moved into the database engine. It just shoots the email address to the specified SMTP server when sending email.
There was also a suggestion of using operator groups or individual operators and then using the sp_notify_operator stored procedure but i believe an email service is expected to work completely different from a notification service.
Auto-resolving has nothing to with operators since the operator still has the email address mapped to it in the background.
I am not sure if there is a solution for this but i believe there is a hand off too .. if you wanted auto-resolving then a default target domain should always be specified which limits sending emails to contacts outside the domain. May we can specifiy the domain as an input to a covering stored procedure which embeds the sp_send_dbmail but still doesn't seem to be really as a solution to auto-resolution.
Any thoughts?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply