时间:2020-09-27来源:www.pcxitongcheng.com作者:电脑系统城
SELECT
mi.mailitem_id,
mi.profile_id,
(SELECT name FROM msdb.dbo.sysmail_profile p WHERE p.profile_id = mi.profile_id) as 'profile_name',
mi.recipients,
mi.copy_recipients,
mi.blind_copy_recipients,
mi.subject,
mi.body,
mi.body_format,
mi.importance,
mi.sensitivity,
ISNULL(sr.send_attempts, 0) as retry_attempt,
ISNULL(mi.from_address, '') as from_address,
ISNULL(mi.reply_to, '') as reply_to
FROM sysmail_mailitems as mi
LEFT JOIN sysmail_send_retries as sr
ON sr.mailitem_id = mi.mailitem_id
WHERE mi.mailitem_id = @mailitem_id
SELECT * FROM msdb.dbo.sysmail_allitems WITH(NOLOCK)
WHERE sent_status != 'sent'
ORDER BY sent_date DESC;
USE msdb;
GO
IF object_id('sp_GetRowSize') is not null
drop procedure sp_GetRowSize
GO
CREATE procedure sp_GetRowSize(@Tablename varchar(100),@pkcol varchar(100))
AS
BEGIN
declare @dynamicsql varchar(MAX)
-- A @pkcol can be used to identify max/min length row
set @dynamicsql = 'select ' + @PkCol +' , (0'
-- traverse each record and calculate the datalength
select @dynamicsql = @dynamicsql + ' + isnull(datalength(' + name + '), 1)'
from syscolumns where id = object_id(@Tablename)
set @dynamicsql = @dynamicsql + ') as rowsize from ' + @Tablename + ' order by 2 desc'
print (@dynamicsql)
END
/******************************************************************************************************
Script Function : 以下示例按条件删除数据库邮件系统中的电子邮件
*******************************************************************************************************/
DECLARE @GETDATE datetime
SET @GETDATE = GETDATE()-2;
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;
GO
DELETE
FROM msdb.dbo.sysmail_allitems
WHERE ((@sent_before IS NULL)
OR ( send_request_date < @sent_before))
AND ((@sent_status IS NULL)
OR (sent_status = @sent_status))
CREATE PROCEDURE
sysmail_delete_mailitems_sp
@sent_before DATETIME = NULL, -- sent before
@sent_status varchar(8) = NULL -- sent status
AS
BEGIN
SET @sent_status = LTRIM(RTRIM(@sent_status))
IF @sent_status = ''
SET @sent_status = NULL
IF ( (@sent_status IS NOT NULL) AND
(LOWER(@sent_status collate SQL_Latin1_General_CP1_CS_AS) NOT IN ( 'unsent', 'sent', 'failed'
, 'retrying') ) )
BEGIN
RAISERROR(14266, -1, -1, '@sent_status', 'unsent, sent, failed, retrying')
RETURN(1) -- Failure
END
IF ( @sent_before IS NULL AND @sent_status IS NULL )
BEGIN
RAISERROR(14608, -1, -1, '@sent_before', '@sent_status')
RETURN(1) -- Failure
END
/* BEGIN ACTIVE SECTION (comment inserted by DPA) */
DELETE
FROM msdb.dbo.sysmail_allitems
WHERE ((@sent_before IS NULL)
OR ( send_request_date < @sent_before))
AND ((@sent_status IS NULL)
OR (sent_status = @sent_status))
/* END ACTIVE SECTION (comment inserted by DPA) */
DECLARE @localmessage nvarchar(255)
SET @localmessage = FORMATMESSAGE(14665, SUSER_SNAME(), @@ROWCOUNT) exec
msdb.dbo.sysmail_logmailevent_sp @event_type=1,
@description=@localmessage
END
2023-03-15
SQLserver存储过程写法与设置定时执行存储过程方法详解2023-03-11
sql语句将数据库一条数据通过分隔符切割成多列方法实例2023-03-06
MySQL中的常用树形结构设计总结最详细的 SQLSERVER ALWAYSON配置教程 一、准备工作: 二、搭建故障转移群集 三、搭建数据库AlwaysOn 四、过程中可能遇到的问题...
2023-03-06
2.1破解的时候,先要断网,然后再操作,按如图打开界面,点击Manage License 2.2点击Activate 2.3打开SQL.Prompt.Keygen.exe,将生成的注册码拷贝到输入框内,然后点击Activate 2.4 继续点击Activate manually 2.5 按如下步骤操...
2023-03-06