Recently, I was asked if I can write and run a specific query and have the results, (if any) emailed to a specific group of people every day? But only send the email if the query returns results. Who wants to open and empty email after all?
最近，有人问我是否可以编写和运行特定查询，并每天将结果（如果有）通过电子邮件发送给特定人群？ 但是仅在查询返回结果时才发送电子邮件。 谁最终想打开和清空电子邮件？
As with any DBA, we need a way to automate this process, as our time is valuable. Who wants to manually run a script, paste it into excel and then email it directly to users every morning?
与任何DBA一样，我们需要一种自动执行此过程的方法，因为我们的时间很宝贵。 谁想手动运行脚本，将其粘贴到excel中，然后每天早晨直接通过电子邮件发送给用户？
First things first, setup Database Mail if you haven’t already. (If you have already, feel free to skip this section).
首先，请设置数据库邮件（如果尚未安装）。 （如果已经拥有，请随时跳过本节）。
The best way to do this is to use the Database Mail Configuration Wizard.
最好的方法是使用数据库邮件配置向导。
Open SSMS, Expand Management and Right-click Database Mail -> Then click “Configure Database Mail”
打开SSMS，展开管理，然后右键单击数据库邮件->然后单击“配置数据库邮件”
Set up Database Mail:
设置数据库邮件：
Give the profile a name and then add an SMTP account.
为配置文件命名，然后添加一个SMTP帐户。
You can use whatever you’d like to for the account name, but you will have to get the server name and SMTP Authentication information from your systems or mail administrator.
您可以使用任意名称作为帐户名称，但是必须从系统或邮件管理员那里获取服务器名称和SMTP身份验证信息。
Select the right profile and press next and finish in the screens that follow:
选择正确的配置文件，然后按下一步，然后在随后的屏幕中完成：
(Feel free to reference for more detailed information on how to setup database mail).
（有关如何设置数据库邮件的更多详细信息，请随时参考 ）。
Remember the profile name, because we’re going to use it when writing our little script.
记住配置文件名称，因为我们将在编写我们的小脚本时使用它。
So, just for the sake of this blog article, let’s come up with a simple script.
因此，仅出于这篇博客文章的目的，让我们提出一个简单的脚本。
Let’s pretend we need a list of employees from the employee table whose current employment status is false, or 0 (if using a bit column date type).
假设我们需要一个来自employee表的雇员列表，该雇员列表的当前雇用状态为false或0（如果使用位列日期类型）。
SELECT Name, EmploymentStatus, StartDate FROM dbo.Employee
WHERE EmploymentStatus = 0
Great, now we need to utilize the mail profile we setup to send this list to the appropriate recipients or distribution list. (Note, in an effort to make our code as clean as possible, it’s highly recommended that you have your mail administrator create a distribution list and add every one of the recipients. Not only does this make the code clean, it will be easy to add new email addresses to the list so that they can receive the reports).
太好了，现在我们需要利用我们设置的邮件配置文件将此列表发送到适当的收件人或通讯组列表。 （请注意，为了尽量使我们的代码更简洁，强烈建议您让邮件管理员创建一个通讯组列表并添加每个收件人。这不仅使代码更简洁，而且很容易在列表中添加新的电子邮件地址，以便他们可以接收报告）。
Now we use the stored procedure (msdb.dbo.sp_send_dbmail) and provide the values for the following parameters. (To get more detailed information regarding this stored procedure feel free to explore this link).
现在，我们使用存储过程（msdb.dbo.sp_send_dbmail）并提供以下参数的值。 （要获取有关此存储过程的更多详细信息，请随时浏览此链接 ）。
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = 'QA_Notification’,
@recipients = ‘human_resources@company.com’
@subject = ‘Employment Status’,
@query = ' SELECT Name, EmploymentStatus, StartDate FROM dbo.Employee
WHERE EmploymentStatus = 0’,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'EmploymentStatus.csv'
End
You can test out the code within a query window, but our goal is to have it send it automatically. Thus we can create a SQL Agent Job and schedule it to run every morning (or as per the request of the users).
您可以在查询窗口中测试代码，但我们的目标是使它自动发送。 因此，我们可以创建一个SQL Agent Job并将其安排为每天早晨运行（或根据用户要求运行）。
But what if there are zero records to report? Do you really think the recipients want to go through the hassle of getting an email if there are zero results to the query? Well we can add one additional piece of code to remedy this. And it’s really quite simple!
但是，如果要报告的记录为零，该怎么办？ 如果查询结果为零，您是否真的认为收件人希望解决发送电子邮件的麻烦？ 好吧，我们可以添加一小段代码来解决这个问题。 这真的很简单！
IF (SELECT COUNT(*) FROM dbo.Employe WHERE EmploymentStatus = 0)> 0
Add this piece of code to the top to verify that there are rows to return. If it’s greater than zero it will go ahead and execute the rest of the script.
将这段代码添加到顶部以验证是否有要返回的行。 如果它大于零，它将继续执行脚本的其余部分。
IF (SELECT COUNT(*) FROM dbo.Employe WHERE EmploymentStatus = 0)> 0
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = 'QA_Notification’,
@recipients = ‘human_resources@company.com’
@subject = ‘Employment Status’,
@query = ' SELECT Name, EmploymentStatus, StartDate FROM dbo.Employee
WHERE EmploymentStatus = 0’,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'EmploymentStatus.csv'
End
Create a SQL Agent job, schedule it and make this piece of code a step. This method is quite versatile as you can use it for more than just sending records and reports to users. You can create custom alerts for yourself (the DBA)! Be creative, query DMV’s or whatever metrics you want, if they reach a certain threshold have the job email you.
创建一个SQL Agent作业，对其进行计划，并使这段代码成为一个步骤。 该方法用途广泛，不仅可以将记录和报告发送给用户，还可以使用。 您可以为自己（DBA）创建自定义警报！ 要有创造力，请查询DMV或您想要的任何指标，如果达到一定阈值，请向您发送工作电子邮件。
Furthermore, you can go ahead and encompass the code into a stored procedure with parameters to make things even easier for yourself. The code would be cleaner, and a lot more flexible depending on what you want the parameters to be. You can even add in some error handling if you’d like.
此外，您可以继续将代码包含在带有参数的存储过程中，以使事情变得更轻松。 根据您想要的参数，代码将更简洁，并且更具灵活性。 如果愿意，您甚至可以添加一些错误处理。
As you can see, the possibilities are endless.
如您所见，可能性是无限的。
Before I conclude this post, I want to quickly touch on SQL Server Job Ownership. Who should “own” a SQL Agent Job and why does it matter?
在结束本文之前，我想快速介绍一下SQL Server作业所有权。 谁应该“拥有” SQL Agent Job，为什么重要？
When a SQL Server Agent Job executes, it will use the server’s own credentials to log into a session. Then it will switch the login of the session to the account that owns the SQL Server Agent job that is currently running. You can have a job potentially run under the incorrect security context. This can result in failures and strange entries into the SQL server log. (Well not strange to SQL server, but strange to whoever is sifting through them). Let’s say that SQL server defaulted to a regular employee username to execute a job and then that employee leaves the company. The job will start failing and the DBA has another problem to fix. This could be a disaster if that account owned many jobs that could include backups, data loads etc…As you can see, it would be quite a mess.
执行SQL Server代理作业时，它将使用服务器自己的凭据登录会话。 然后，它将会话的登录名切换到拥有当前正在运行SQL Server代理作业的帐户。 您可能有可能在不正确的安全上下文下运行作业。 这可能会导致失败和进入SQL Server日志的奇怪条目。 （对SQL Server来说并不奇怪，但是对筛选它们的人来说却很奇怪）。 假设SQL Server默认使用常规员工用户名执行工作，然后该员工离开公司。 作业将开始失败，并且DBA还要解决另一个问题。 如果该帐户拥有许多工作，其中可能包括备份，数据加载等，那将是一场灾难。正如您所看到的，这将是一团糟。
It is best to have a dedicated service account that is a member of the fixed sysadmin server role in SQL server take ownership of all SQL Jobs.
最好有一个专用服务帐户，该帐户是SQL Server中固定sysadmin服务器角色的成员，并拥有所有SQL作业的所有权。
It’s a good idea to get in the habit of setting this option upon creating a new job. When in the job properties simply go to the General menu and look at “Owner”. If it’s not already set to the right account simply click the box next to the field and change it. In this example, the owner is SA (or you can use the service account for SQL Server or SQL Agent).
养成在创建新工作时设置此选项的习惯是一个好主意。 在作业属性中时，只需转到常规菜单，然后查看“所有者”。 如果尚未将其设置为正确的帐户，只需单击该字段旁边的框并进行更改。 在此示例中，所有者是SA（或者您可以将服务帐户用于SQL Server或SQL Agent）。
If you want to check who owns the all SQL jobs on your servers you can create a query that pulls data from the msdb.dbo.sysjobs and join it to the master.syslogins table. I’ve used something like this in the past:
如果要检查谁拥有服务器上所有SQL作业，可以创建一个查询，该查询从msdb.dbo.sysjobs中提取数据并将其联接到master.syslogins表中。 我过去曾使用过类似的方法：
select
j.name AS [JobName],
l.name AS [LOGIN]
from [msdb].[dbo].[sysjobs] AS J
join master.sys.syslogins AS l on j.owner_sid = l.sid
And if you get creative, you can make an alert for yourself using methods from the first part of this post. See the flexibility that we have now?
而且，如果您很有创造力，则可以使用本文第一部分中的方法来为自己发出警报。 看到我们现在拥有的灵活性吗？
For more information regarding SQL Agent account settings please feel free to go here
有关SQL代理帐户设置的更多信息，请随时转到此处
I hope that you found the information in this post useful and that you can take the general ideas to create customized solutions for yourself. Please feel free to share your creative solutions in the comments below or email me directly! I would like to hear from you!
我希望您发现这篇文章中的信息有用，并希望您可以采用一般思路为自己创建定制的解决方案。 请随时在下面的评论中分享您的创意解决方案，或直接给我发送电子邮件！ 我希望收到您的来信！
参考资料 (References)
- Configure Database Mail配置数据库邮件
- sp_send_dbmail (Transact-SQL)sp_send_dbmail（Transact-SQL）
- Select an Account for the SQL Server Agent Service为SQL Server代理服务选择一个帐户
2381
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
