I was answering a question on a forum the other day, and the person asking the question was puzzled about how to send an email to an operator (i.e. msdb operator) rather than to just any other email address.
The reason he wanted to do that was that he wanted to use the Agent operator system to define a name. He didn't want to hard-code a recipient name in his code. By having it as an operator, it could be changed later, independent of the code.
Doing that isn't so hard. You just need to retrieve the operator's email address from msdb.dbo.sysoperators before sending the mail. Here's an example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE PROCEDURE dbo.EmailOperator @OperatorName sysname, @EmailBody nvarchar(max), @EmailSubject nvarchar(255), @EmailProfileName sysname = NULL -- can use default AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE @OperatorEmailAddress nvarchar(256) = (SELECT so.email_address FROM msdb.dbo.sysoperators AS so WHERE so.[name] = @OperatorName); IF @OperatorEmailAddress IS NOT NULL BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = @EmailProfileName, @recipients = @OperatorEmailAddress, @body = @EmailBody, @subject = @EmailSubject ; END; END; GO |
And then send the email like this:
|
1 2 3 4 5 |
EXEC dbo.EmailOperator @OperatorName = N'OnCallDBAs', @EmailBody = 'It''s nearly time to plan the roster for Christmas again', @EmailSubject = 'Important message for all DBAs'; GO |
And as BWoodburn noted in the comments, there is also a built-in function that you might be able to call:
It offers slightly different options.
I hope that one of these helps someone.
Hello everyone
I want to know if transaction replication works with always encrypt columns
Thank you
Hi Yacov, no, unfortunately. You can find that officially mentioned here.
You can also use: msdb.dbo.sp_notify_operator
I just discovered this today.
Excellent. I've updated the post.
can you at the @query option to work in the body of the text?
Have to admit that I haven't used that option in a very, very long time. There also used to be a mechanism where you could email a query to SQL Server, it would execute it, and email back the results. Long gone now though.
I ask you for grapes and you tell me about wine I cannot have!!!!!
lol thank you I'll have to have a look. I got 1 DEV that had sa a few years back and now we are trying to clean up old profiles……such fun ..all day its outlook and dbmail *smh*
I just checked, and it looks like the @query parameter still works. When you use it, it logs on, runs the query, and sends the results of the query in the email. The results can be sent as an attachment or included in the body of the email. The catch is the authentication. The limitation says:
When using @query parameter, user that executes sp_send_dbmail must be SQL login or directly mapped to principal (login) of Azure AD or AD. If user is member of Azure AD group or AD group it will not be able to execute the query. This is due to Azure SQL Managed Instance impersonation and EXECUTE AS limitations.
We'd normally do this via group membership, not via a direct SQL login. (We try to eliminate direct individual SQL logins). So that wouldn't work for us, but depending upon your situation, it might work. They removed the "listen to emails in an inbox" part, but left the "send the results via email" part.