使用邮件发送执行时间久的SQL语句

ALTER proc [dbo].[usp_EmailLongRunningSQL]
as
begin
declare@LongRunningQueries AS TABLE
(
  lrqId int IDENTITY(1,1) PRIMARY KEY,
  spid int NULL,
  batch_duration bigintNULL,
  program_namenvarchar(500) NULL,
  hostname nvarchar(100) NULL,
  loginame nvarchar(100) NULL,
  sqltext nvarchar(max) NULL
)
-- variabledeclaratuions
DECLARE @exectime DATETIME
DECLARE @tableHTMLNVARCHAR(MAX)
DECLARE @Handle VARBINARY (85)
DECLARE @SPID INT
DECLARE @sqltext NVARCHAR(MAX)
DECLARE @timeLimitsmallint
declare @Sub as varchar(100)
set @Sub = @@servername + 'Long Running  Query found'
SET @timeLimit=(3*60) -- minutes
 
-- WAITFOR DELAY'00:01:05' -- uncomment for testing (1min:5sec)
 
-- populate thetable with execution info, you don't have to use top 1
INSERT INTO @LongRunningQueries(spid, batch_duration, program_name, hostname, loginame)
 
SELECT top 5
    P.spid
  , convert(bigint,DateDiff(ms,P.last_batch,getdate()))/1000
  , P.program_name
  , P.hostname
  , P.loginame
FROM master.dbo.sysprocesses P WITH(NOLOCK)
WHERE (P.spid > 50)
AND P.status NOT IN('background', 'sleeping')
AND P.cmd NOT IN('AWAITING COMMAND','MIRROR HANDLER','LAZY WRITER','CHECKPOINT SLEEP','RA MANAGER')
AND convert(bigint,DateDiff(ms,P.last_batch,getdate()))/1000 >@timeLImit
 
 
-- use a cursorto update the sqltext for each pid
DECLARE @lrqId int
DECLARE mycur cursor for
  SELECT lrqId from @LongRunningQueries
  ORDER BY lrqId
 
OPEN mycur
FETCH NEXT FROM mycur INTO @lrqId
 
WHILE @@FETCH_STATUS=0
BEGIN
 
  SET @SPID =(SELECT spid from @LongRunningQueries WHERElrqId=@lrqId)
 
  -- get the SQL theSPID is executing
  SELECT@Handle = sql_handleFROM master.dbo.sysprocesses WHEREspid = @SPID
 
  UPDATE@LongRunningQueries
  SET sqltext =(SELECT text FROM sys.dm_exec_sql_text(@Handle))
  WHERE lrqId = @lrqId
 
  FETCH NEXT FROM mycur INTO @lrqId
END
CLOSE mycur
DEALLOCATE mycur
 
DELETE FROM @LongRunningQueries WHEREsqltext IS NULLOR sqltext='' OR  program_nameLIKE '%DatabaseMail%'
 
IF EXISTS(SELECT * FROM@LongRunningQueries WHERE sqltext IS NOT NULL OR sqltext<>'')
BEGIN
-- populate atable with it's info and mail it
SET @tableHTML=
  N'<H1>LongRunning  Querys</H1>' +
  N'<tableborder="1">' +
  N'<tr><th>SPID</th>'+
  N'<th>Duration</th>'+
  N'<th>Application</th>'+
  N'<th>HostName</th>'+
  N'<th>Login</th>'+
  N'<th>SQLExecuting</th></tr>' +
  CAST(( SELECT td = T.spid, '',
  td = T.batch_duration, '',
  td = T.[program_name], '',
  td = T.hostname, '',
  td = T.loginame, '',
  td = T.sqltext, ''
  FROM
  @LongRunningQueries T
  FOR XML PATH('tr'), TYPE
  ) AS NVARCHAR(MAX) ) +
  N'</table>'
 
-- if @tableHTMLis NULL, mail will not get sent
EXEC msdb.dbo.sp_send_dbmail,
@recipients= 'smith.liu@126.com'
@body = @tableHTML,
@body_format = 'HTML';
END
 
end