SQL定制警报(SQL custom alert)

所以我试图建立在Microsoft SQL 2014的自定义警告我想给我的球队，每当事情已经坐在队列超过30分钟的电子邮件。

我保证我研究它了不少，但问题的其他人发布类似这样在这里没有帮助。

我设置数据库邮件配置文件，并发送测试电子邮件。有用。

然后我去了SQL警告管理建立一个新的警报。

我离开了命名空间来它给我的默认值：\ \ ROOT \微软\的SqlServer \ ServerEvents \ MSSQLServer和我的脚本如下：

if (exists (Select 1
            From ____
            where Status = 'WAITING' and
                  (GETDATE() - [ITIME]) > 0.5 * (1.0/24)
           )
   )
BEGIN

EXEC msdb.dbo.sp_send_dbmail
  @recipients='_______',
  @body='Attention: A job has been sitting in the _____ queue for longer than 30 minutes.', 
  @subject ='Queue Time Expiration',
  @profile_name ='__________',
  @query =
     'USE ___
     (select * from _____] where Status=''WAITING'' and (GETDATE() - [ITIME])>0.02)'

END

我得到这个错误：SQLServerAgentError：WMI错误：0x80041058的@wmi_query不能提供的@wmi_namespace执行。验证在查询中选择一个事件类的命名空间，并且查询有正确的语法存在。

我不知道，如果有什么毛病我查询本身，因为我没写过这种类型的脚本之前和我还挺补差，我去，或者如果它的命名空间。我没有更改命名空间，它只是默认。我真的不知道放在那里，这正是它在我下面的教程显示。

编辑：在我的脚本语法固定从下面的建议，仍然没有工作，虽然

Answer 1:

I wanted to share how I worked around this problem before I close this question. I ended up using a different method to solve this problem instead of the one I was trouble-shooting for. Gordon Linoff's critique of my script was helpful anyways, and I think Rajesh had the right idea as well but I found a way that was easier for me.

The goal of my problem was simple: I wanted my team to receive an e-mail every time a query returned some results. In my case, whenever there were any objects that had been sitting waiting in the queue for longer than 30 minutes. I apologize for the lack of details, I don't want to violate company policies. I think that this can be accomplished through the alerts system, however I never managed it. Instead I wrote a job that includes as a step a script that checks for any objects that meets my criteria, and then emails my team if there are any. This is probably obvious to DBAs, but I'm super new to this stuff so excuse my ignorance.

For anyone else who is stuck, here's what I did. This tutorial is for the 2014 edition, and I apologize but I can't include the images since I don't have enough reputation yet. I recommend running these steps on a test database first.

1.) Write your query

Create a "New Query", and start by writing your own query that checks the database for the condition you want your automated e-mails to notify based on. Just a simple select statement. We're going to modify our query slightly to record the number of records that meet the criteria in the "where" clause in the variable @recordCount. We aren't doing anything with recordCount yet, but we do execute the script to see that the syntax is correct. Go ahead and make your own recordCount variable and assign it to the count of your query results as well and execute it to check the syntax. We're going to have to set our query aside for now so we can set up e-mail in the database, but we will come back to it in Step 4, so be sure to save it or leave it open.

Example:

declare @recordCount int
Select @recordCount = isnull(count(*), 0)
From (table)
where (conditions)

Simple enough.

Step 2: Create an e-mail profile

We need to create a profile for MSSQL to send e-mails from. This is not the e-mail address you want the notifications sent to, however they can be the same e-mail address. In the object explorer, expand the server node, then expand the "Management" node. You'll see something called "Database mail." Double click it to launch the Database Mail Configuration Wizard. Press "next" at the intro page. On the second page of the Database Wizard, you'll want to choose the first option (it should say something about setting up a new profile).

•Click "Next."

•Give your profile a name. Remember, this is the name of the e-mail sender, not the recipient.

•Fill in an appropriate description.

•Click "Add."

•In the pop-up window that appears, choose "New Account."

•In the "New Database Mail Account" window that appears, give your account a name and description.

•In the "Outgoing Mail Server" section, specify the e-mail address of the sender and give it a display name.

•You'll need to know the SMTP server of the e-mail account you're using. If it is an @intel.com account, write smtp.intel.com as shown below. Google Mail uses smtp.google.com, and yahoo uses smtp.mail.yahoo.com.

•For a list of smtp server names for the most popular e-mail providers, follow this link: http://www.serversmtp.com/en/what-is-my-smtp

•You'll also need to know the port number.

•Under the "Authentication" section, you'll most likely want the first option, though if you know the e-mail and password logon for this account, you can choose the second option and provide them.

•Click OK.

•On the next screen, choose to make the profile public if this is reasonable, and in the far right column choose for this profile to be the default.

•Click Next.

•On the next page, leave the default options and click "Next."

•On the final page, review your changes and click "Finish."

Step 3: Enable e-mail on the server agent

•If you expand the server and scroll down, you'll see the "SQL Server Agent." Right-click this and choose "properties."

•In the sidebar on the left, choose "Alert System."

•Under "Mail session," make sure "Enable mail profile" is checked. "Mail system:" should be set to "Database Mail," and you'll want to choose the profile you created in step 2 for the mail profile. •At the bottom of the "Pager e-mails" section, check "include body of e-mail in notification message."

•Choose OK.

•You'll want to restart the agent. Back in the object explorer, right-click "SQL Server Agent" again and choose "start" or "restart."

•尽管电子邮件已在代理上启用，你可能仍然有数据库禁用邮件。创建一个“新查询”，然后复制粘贴下面简短的脚本。这将在“数据库邮件XPS”变量设置为1，这意味着它已启用：sp_configure显示高级选项，1;

GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

•滚动备份到在服务器名称>管理对象浏览器中的“数据库邮件”选项。右键单击并选择“发送测试电子邮件”。在弹出的窗口中，请确保您的新的配置文件的帐户被选为轮廓，并在“To：”行指定自己的E-mail地址。 •您应该在数分钟内收到测试电子邮件。

第4步：写剧本

•我们打算从步骤1在我们的查询扩展。

•据推测，如果该查询返回的一些记录，我们希望得到通知。因此，我们扩展我们查询到了下面的脚本：

declare @recordCount int
 Select @recordCount = isnull(count(*), 0)
From (your table)
 where (your criteria)

IF (@recordCount > 0)
 Begin
 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'the name of the profile you created in step 2',
 @recipients = 'your email address',
 @query = 'select * from (your table - same as above) where (your criteria - same as above)' ,
 @subject = 'your email subject',
 @Body = 'your e-mail message'
 End

•在这个新的脚本的顶部，我们看到在我们准确的查询。在它下面，有一个条件语句。它说，如果有符合我们的查询条件的所有记录，然后执行send_dbmail命令。继续前进，在一个新的查询复制粘贴此脚本，在必要的信息填写。由于这是一个测试，你可能想给它自己的电子邮件地址收件人。此外，请确保你给它，你给你创建的配置文件的确切名称。

•请注意：如果你已经忘了你创建的配置文件的名称，或者如果你不知道确切的拼写或外壳，在对象资源管理器中右键单击SQL Server代理，并选择“属性”。在左侧边栏中选择“报警系统”。在页的顶部，配置文件的名称将被指定。一旦你拥有了它，取消此窗口。

•一旦更换以粗体显示的部分用自己的材料，突出整个脚本并执行它。如果没有发现任何记录找到符合条件的，你会得到消息“成功完成指令（S）。” 如果发现了至少一个记录，你会得到消息“邮件排队。”

•如果你得到的消息“成功完成指令（S），”继续前进，创造一个虚拟的记录，将符合条件并触发电子邮件，然后再次运行该脚本。

•您会收到电子邮件在几分钟内。请注意，电子邮件是丑陋的格式不正确。现在我们来解决这个问题。

•要格式化我们的电子邮件，我们需要重新调整我们的脚本。在下面的例子中，我使用HTML，使查询结果中一个简单的表格显示格式的电子邮件。需要改变的东西都在大胆。

declare @recordCount int
Select @recordCount = isnull(count(*), 0)
From **(table)**
where **(conditions)**



IF (@recordCount > 0)
Begin
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT **[(column)]** AS 'td','',**[(column)]** AS 'td','',
**[(column)]** AS 'td','', **(column)** AS 'td'
FROM **(table)**
where **(conditions)**
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='**(Message at beginning of email)**' <br> <br>      <br>
 <html><body><H3 style="color:#3333FF">**(Label for your table)**</H3>
 <table border = 1> 
 <tr>
 <th> **(Column header text)**</th> <th> **(Column header text)** </th> <th> **(Column header text)** </th>     <th> **(Column header text)** </th></tr>'

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = '**(profile name from last step)'**,
 @recipients = '**(recipient e-mail)**',
 @subject = '**(Email subject)**',
@Body = @body,
 @Body_format='HTML'
 End

•最重要的事情是，你添加变量@Body_format并将其设置为HTML。然后，你可以添加一个变量@Body到你的脚本，并将其设置为您在电子邮件想要的HTML内容。不要忘记设置@Body等于底部的EXEC块内@body。其他的一切只是简单的加价。其结果是一个简单的表格，但你可以改变的标记，以任何你喜欢的。

•来吧，保存你的脚本。我们将在最后的步骤中使用它。

第5步：设置一个作业运行我们的脚本

•我们几乎完成了！现在，我们的脚本工作，我们需要做的是建立一个作业计划运行此脚本。在服务器下的对象资源管理器中，展开SQL Server代理。右击“作业”，然后选择“新的工作。”

•给你的工作名称和描述，让业主到默认的登录和“类别”可保留设置为“[未分类（本地）]。” 确保“启用”被选中。

•在左侧边栏中，选择“步骤”。

•在底部，选择“新建”。

•在弹出的窗口中，给步骤的名称。设置 “类型” 为 “Transact-SQL脚本（T-SQL）”，并选择相应的数据库。 “运行方式”可以留空。

•点击“打开”按钮，并选择您在步骤4的工具将你的脚本自动填充保存脚本。你可以点击“解析”，以检查你的脚本，最后一次的语法。

•单击确定。

•在新的工作窗口的左侧边栏，选择“时间表”。在底部，点击“新建”。

•确保“启用”被选中，你已经给你的计划的名称。

•您如何安排自己的时间是你的。您只能在工作日或全天候计划作业运行一次，或将其设置为重复，并允许它执行每月，每周，每天，分或秒，无限期或直到特定的结束日期。

•在审查在“摘要”下方的说明，以确保这是你想要的。单击确定。

•早在“新作业”窗口中，单击“确定”。

•确保SQL Server代理正在运行，扩大就业机会。你现在应该有看到你的工作列表，如果不是你可以右键单击“作业”，然后选择“刷新”。

•在列表中找到你的工作，用鼠标右键单击它，然后选择“步骤开始工作......”

•你的工作现在正在运行，并会继续运行，但已将其定！如果你需要编辑它，你可以简单地双击它。

还有疑问？ •本教程由以下两种资源创造：

1）微软的数据库邮件文档： http://msdn.microsoft.com/en-us/library/ms187605.aspx

2）很抱歉，无法发布第二资源链接，因为我没有名声呢。只是谷歌的Microsoft SQL Server故障排除。

谢谢大家。

Answer 2:

你可能有其他的问题，但你if代码是没有意义的。您正在执行创建结果集的查询，然后你有一个if 。这应该做你的那部分代码想要的东西：

declare @WaitTime datetime;

Select @WaitTime = [ITIME]
From ______________
where Status='WAITING' ;

IF (GETDATE() - @WAITTIME) > 0.02 . . .

我假定你已经完成了计算和0.02天你真正想要的。否则，你会使用0.5 / 24为常数。

您也可以替换变量，查询，以及如果与：

if (exists (Select 1
            From ______________
            where Status = 'WAITING' and
                  IF (GETDATE() - ITIME) > 0.5 * (1.0/24)
           )
   )

Answer 3:

您使用了错误的命名空间中， CIM_DataFile WMI类是一部分\root\CIMV2命名空间，而不是\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER

用在查询使用WMI事件错误SQL服务器警报解决错误的是

EXEC msdb.dbo.sp_add_alert @name=N'SimpleFolderWatcher', @message_id=0,
@severity=0,@enabled=1,@delay_between_responses=0, 
@include_event_description_in=0,@category_name=N'[Uncategorized]',  
@wmi_namespace=N'\\.\root\cimv2', 
@wmi_query=N'SELECT * FROM __InstanceCreationEvent WITHIN 1 
WHERE TargetInstance ISA ''CIM_DataFile'' 
AND TargetInstance.Drive = ''C:'' 
AND TargetInstance.Path=''\\testfolder\\'' 
AND TargetInstance.Name LIKE ''C:\\%'' ', 
@job_id=N'0-0-0'