創建作業以在表值大於 x 時使用 sp_send_dbmail 發送警報

問題

SMScounter.Counter用於累積服務的使用情況。該服務的程序化上限為 1000。我想在超出此上限時提醒使用者。

表創建

CREATE TABLE [dbo].[SMSCounter](
 [Counter] [int] NOT NULL,
 [CounterDate] [datetime] NOT NULL )

範例數據

380, 2012-09-10 00:00:00.000
177, 2012-10-14 00:00:00.000
999, 2012-11-16 00:00:00.000

作業腳本

下面的腳本是我嘗試創建一個計劃的作業，如果SMScounter.Counter大於 950，它將每小時檢查一次。

USE DATABASEname
select (Counter) as CHECKVALUE from SMSCounter where smscounter.CounterDate  = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)    
IF CHECKVALUE > 950

BEGIN

EXEC msdb.dbo.sp_send_dbmail
 @recipients=N'scott@mydomain.com',
 @body='1000 Daily SMS cap near', 
 @subject ='1000 Daily SMS cap near',
 @profile_name ='SCOTT',
 @query =
   'USE DATABASEname
    (select * from SMSCounter where CounterDate = GETDATE())'

END

問題

1. 閱讀時如何格式化GETDATE以檢查dd/mm/yy和忽略時間部分SMScounter.CounterDate？（上面的腳本已編輯，現在可以使用）。
2. 我怎樣才能讓IF聲明與CHECKVALUE950 進行比較？

我使用的是 SQL Server 2008 標準版。

您可以使用convert (date, GETDATE()) 僅獲取“日期”部分：

select GETDATE(), convert (date, GETDATE())
----------------------- ----------
2012-11-16 08:58:20.750 2012-11-16

而且，如果您只想比較“日期”部分，則可能值得將表中的數據類型更改為DATE，以節省每次查詢時都必須進行轉換。

對於IF語句，您需要傳入一個要檢查的值，然後包裝要在BEGIN和END語句中執行的程式碼。這是使用 sysobjects 表的範例：

declare @cnt integer
select @cnt=COUNT(1) from sysobjects

if @cnt>50
begin
 select 'hello'
end

如果你的數據庫中有超過 50 個對象，它會打招呼

所以在你的情況下：

USE DATABASEname
go
declare @counter integer

select @counter=MAX([Counter]) from SMSCounter where convert(date,CounterDate) = convert(date,GETDATE())

IF @counter > 950
BEGIN
 EXEC msdb.dbo.sp_send_dbmail
 @recipients=N'scott@mydomain.com',
 @body='1000 Daily SMS cap near',
 @subject ='1000 Daily SMS cap near',
 @profile_name ='SCOTT',
 @query =  '(select * from DATABASEname.dbo.SMSCounter where convert(date,CounterDate) = convert)(Date,GETDATE()))'
END

我在MAX您的檢查查詢中添加了一個函式，因為它只會返回包含當天最大值的單行，否則它將從所有匹配的行中返回一個隨機值。如果每次寫入表時都覆蓋當天的值，那麼這是多餘的。

引用自：https://dba.stackexchange.com/questions/28780