hi Experts.
I'm trying to set the query to send email ONLY when it returns records of blocks and i cant seem to get this going. any help would be appreciated. TIA!
declare @blocks varchar(max)
set @blocks = (SELECT spid,
sp.[status],
loginame [Login],
hostname,
blocked BlkBy,
sd.name DBName,
cmd Command,
cpu CPUTime,
physical_io DiskIO,
last_batch LastBatch,
[program_name] ProgramName
FROM master.dbo.sysprocesses sp
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
WHERE loginame = 'user1'
AND (sp.[status] NOT LIKE 'sleeping%')
for xml raw('tr'), Elements)
Set @blocks = '<table>' + @blocks + '</table>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB_Mail_Profile',
@recipients = 'email@yourmail.com',
@subject = 'blocks by user1',
@body = @blocks,
@body_format = 'HTML';