When you have automated backup jobs running on your database server, sometimes you forget that they are even running. Then you forget to check to see if they are running successfully, and don’t realize until your database crashes and you can’t restore it since you don’t have a current backup.
当您在数据库服务器上运行自动备份作业时，有时您会忘记它们甚至正在运行。 然后您忘记检查它们是否成功运行，并且直到数据库崩溃并且由于没有当前备份而无法恢复数据库时才意识到。
That’s where email notifications come in, so you can see the job status every morning when you are sipping your coffee and pretending you are working.
那是电子邮件通知的来源，因此您每天早晨可以在喝咖啡，假装工作时查看工作状态。
SQL Server provides a built-in method of sending emails, but unfortunately it requires you to have Outlook and a profile installed on the server, which isn’t necessarily the ideal way to send an email. Thankfully there is another method, that involves installing a stored procedure on your server that will allow you to send email via SMTP.
SQL Server提供了一种内置的发送电子邮件的方法，但是不幸的是，它要求您在服务器上安装Outlook和配置文件，这不一定是发送电子邮件的理想方法。 值得庆幸的是，还有另一种方法，涉及在服务器上安装存储过程，该存储过程将允许您通过SMTP发送电子邮件。
Download the sp_SQLNotify Stored Procedure here.
You will want to edit one line in the stored procedure to put the IP address of your SMTP server:
您将需要在存储过程中编辑一行以放置SMTP服务器的IP地址：
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields(“http://schemas.microsoft.com/cdo/configuration/smtpserver”).Value’, ‘10.1.1.10’
EXEC @hr = sp_OASetProperty @iMsg，'Configuration.fields(“ http://schemas.microsoft.com/cdo/configuration/smtpserver”).Value'，'10 .1.1.10'
Install the stored procedure into the master database, so it can be easily used from wherever needed.
将存储过程安装到master数据库中，以便可以在任何需要的地方轻松使用它。
Open up the SQL Server Agent \ Jobs list, and select the properties for the job you are trying to create a notification for:
打开“ SQL Server代理\作业”列表，然后选择要为其创建通知的作业的属性：
Click on the Steps tab, and you should see a screen that looks like this:
单击“步骤”选项卡，您应该看到一个类似以下的屏幕：
Click the New button to create a new job step. We will use this step to send the email notification on success.
单击“新建”按钮以创建新的作业步骤。 我们将使用此步骤发送成功的电子邮件通知。
Step Name: Email Notification Success
步骤名称：电子邮件通知成功
Enter this SQL into the Command window as seen below. You will want to customize the email addresses and message subject to match your environment:
将此SQL输入到“命令”窗口中，如下所示。 您将要自定义电子邮件地址和消息主题以匹配您的环境：
exec master.dbo.sp_SQLNotify ‘server@localserver.com’,’admin@localserver.com’,’Backup Job Success’,’The Backup Job completed successfully’
exec master.dbo.sp_SQLNotify'server@localserver.com'，'admin@localserver.com'，'备份作业成功'，'备份作业成功完成'
Click OK and then click the New button again to create another step. This will be the failure notification step.
单击确定，然后再次单击新建按钮以创建另一个步骤。 这将是故障通知步骤。
Step Name: Email Notification Failure
步骤名称：电子邮件通知失败
SQL:
SQL：
exec master.dbo.sp_SQLNotify ‘server@localserver.com’,’admin@localserver.com’,’Backup Job Failure,’The Backup Job failed’
exec master.dbo.sp_SQLNotify'server@localserver.com'，'admin@localserver.com'，'备份作业失败，'备份作业失败'
Now the idea is to make the items follow a specific workflow. First click Edit on step 1, and set the properties as shown here:
现在的想法是使项目遵循特定的工作流程。 首先在步骤1上单击“编辑”，然后按如下所示设置属性：
What we are saying is that on success, go to the success step, and on failure, go to the failure step. Pretty simple stuff.
我们的意思是，成功时转到成功步骤，失败时转到失败步骤。 很简单的东西。
Now edit the second step, the one labled “Email Notification Success”, and set the properties as seen here:
现在，编辑第二步，将其标记为“电子邮件通知成功”，并按如下所示设置属性：
We are saying that if the notification job is successful, then just quit the job without running step 3. If we don’t specify this, then we will end up getting two emails, one with success and one with failure.
我们说的是，如果通知作业成功，则不执行步骤3就退出该作业。如果未指定，则最终将收到两封电子邮件，其中一封成功，而一封失败。
Now edit the third step, the one labled “Email notification failure”, and set the properties as seen here:
现在，编辑第三步，将其标记为“电子邮件通知失败”，并按如下所示设置属性：
Now your job steps should look like this:
现在您的工作步骤应如下所示：
You should now have email notifications in your inbox for either success or failure.
现在，您的收件箱中应该有关于成功或失败的电子邮件通知。
Note: The stored procedure used in this article was found here, although that may not be the original source.
注意：本文使用的存储过程位于此处，尽管可能不是原始来源。
Download the sp_SQLNotify Stored Procedure here.