工作中需要对一个每天同步数据的报表数据库做监控,每次发现页面上数据没有了再去查搬运日志或数据库表很繁琐,无意中看到了数据库的邮件发送功能,做了一个存储过程使用动态sql来对所有表做一个检测,发现没有数据的表发送邮件和表名,这样直接根据邮件去检查单独异常的表搬运即可。
之后还利用公司的短信发送平台,做了一个短信发送,更加及时了,但是那个需要开发支持,我不清楚细节,这里就不发了。
创建数据库的邮件功能是从网上看来的,这里不做详述,只贴个例子
--1.启用Database Mail扩展存储过程
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH override
GO
sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE WITH override
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH override
GO
--2.添加account
exec msdb..sysmail_add_account_sp
@account_name = 'test' --邮件帐户名称SQL Server 使用
,@email_address = 'test'@163.com' --发件人邮件地址
,@mailserver_name = 'mail.btte.net' --邮件服务器地址
,@mailserver_type = 'SMTP' --邮件协议SQL 2005只支持SMTP
,@port = 25 --邮件服务器端口
,@username = 'test' --用户名
,@password = '******' --密码
--3.添加profile
exec msdb..sysmail_add_profile_sp
@profile_name = 'test_mail'-- profile 名称
,@description = 'dba test mail profile'-- profile 描述
,@profile_id = null
--4.映射account和profile
exec msdb..sysmail_add_profileaccount_sp
@profile_name = 'test_mail'-- profile 名称
,@account_name = 'test'-- account 名称
,@sequence_number = 1-- account 在profile中顺序
以下是检测的存储过程语句,
USE [jeeplus]
GO/****** Object: StoredProcedure [dbo].[report_table_check] Script Date: 2017/11/21 15:52:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*2017年11月20日14:43:06改进,检测报表数据是否正常更新*/
ALTER PROCEDURE [dbo].[report_table_check]
AS
BEGIN
SET NOCOUNT ON
declare @check_reslut nvarchar(1000)
DECLARE Cursor_Table CURSOR FOR
/*1.用游标Cursor_Table,遍历需要检测的表,这里我列出了需要检测的表,因为报表数据库不是所有的表格都需要检查*/
SELECT name from jeeplus.sys.sysobjects WHERE xtype = 'u' AND name in(
'base_account_visual',
'fact_IFMS',
'log_sys_Community',
'Report_Accounts_list',
'Report_community',
'Report_community_operate',
'Report_Consult_NEW',
'Report_Consult_Renewals',
'Report_cti_statistics',
'Report_Index_Ship_statics',
'Report_Install_list',
'Report_InstallForm_TBL',
'Report_Person',
'Report_product_sale',
'Report_Reminder',
'Report_Repair_list',
'Report_RepairForm',
'Report_RepairStatistics',
'Report_revenue',
'Report_Simple_Day',
'Report_statistics',
'report_zcqk',
'WorkGroup_TBL',
'sys_Community'
,'jeeps_workflow_returns_refund'
,'jeeps_workflow_returns_list'
,'jeeps_account_audit'
,'jeeps_workflow_complaint_list'
,'jeeps_workflow_list'
,'jeeps_workflow_extensive_fault_list'
,'jeeps_centralized_input_workload_list'
,'jeeps_workflow_Account_workload_list'
,'jeeps_workflow_repair_workload_list'
,'jeeps_workflow_technology_filtration_workload_list'
,'jeeps_Reminder_list'
)
/*这里我列出了需要检测的表,因为报表数据库不是所有的表格都需要检查*/
declare @time datetime
DECLARE @DynamicSQLText NVARCHAR(MAX)
DECLARE @tableName NVARCHAR(200);
DECLARE @CurrentTableCount NVARCHAR(4000)
DECLARE @columnName nvarchar(50)
set @columnName=''
set @DynamicSQLText=''
set @time= dateadd(ms,-1,DATEDIFF(DAY, 1, GETDATE() ))
OPEN Cursor_Table
FETCH NEXT from Cursor_Table INTO @tableName
WHILE @@fetch_status = 0
BEGIN
print QUOTENAME(@tableName)
set @columnName=''
set @DynamicSQLText=''
/*对表里的字段做一个排序,因为当时设计的时候没考虑这一点,所以每个表的时间字段名字都不一样,做了一个条件排查*/
if not exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name in ('create_date','dispatch_time','logtime','create_time','statisticstime','timee','createtime','time')) /*这里列出来所有表用于比较的时间字段,如果都没有直接返一个报错,后续添加即可*/
begin
print @tableName+'没有timee字段'
end
else
begin
IF exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name='timee')
BEGIN
set @columnName='timee'
END
ELSE IF exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name='statisticstime')
BEGIN
set @columnName='statisticstime'
END
ELSE IF exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='create_time')
BEGIN
set @columnName='create_time'
END
ELSE IF exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='logtime')
BEGIN
set @columnName='logtime'
END
ELSE IF exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='create_date')
BEGIN
set @columnName='create_date'
END
ELSE IF exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='createtime')
BEGIN
set @columnName='createtime'
END
ELSE IF exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='dispatch_time')
BEGIN
set @columnName='dispatch_time'
END
ELSE IF exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='dispatch_time')
BEGIN
set @columnName='dispatch_time'
END
ELSE IF exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='time')
BEGIN
set @columnName='time'
END
PRINT @columnName
set @CurrentTableCount= '根据'+ QUOTENAME(@columnName) + '字段判断,以下表未更新最新数据:[jeeplus].[dbo].'+ QUOTENAME(@tableName)
set @DynamicSQLText =
'DECLARE @maxtime datetime
SELECT @maxtime=convert (datetime,max('+QUOTENAME(@columnName)+'),120) FROM [jeeplus].[dbo].' + QUOTENAME(@tableName) + '
IF @maxtime>=@TIMET
BEGIN
print ''以下表有最新数据: ' + QUOTENAME(@tableName) + '''
END
else
BEGIN
exec msdb..sp_send_dbmail @profile_name = ''check'',@recipients =''z****@163.com'',@subject =''数据库表数据异常提醒'',@body =@CurrentTableCountT ,@body_format =''TEXT''
print @CurrentTableCountT
END
'
EXEC sp_executesql @DynamicSQLText ,N'@CurrentTableCountT NVARCHAR(4000),@TIMET datetime',@TIMET=@time ,@CurrentTableCountT=@CurrentTableCount
end
FETCH NEXT from Cursor_Table INTO @tableName;
END
CLOSE Cursor_Table;
DEALLOCATE Cursor_Table;
END