declare @emailSubject varchar(100),
@columnHeaders varchar(1000),
@tableHTML nvarchar(max)
select @emailSubject = ‘report’, — Subject of Email
@columnHeaders = ‘session_id </th><th> DB_Name </th><th> host_name </th><th> program_name </th><th> client_interface_name </th><th> login_name </th><th> nt_domain </th><th> nt_user_name </th><th>
connect_time </th><th> login_time</th><th>’ — Column headers (must put </th><th> between each header)
if (select COUNT(*) from sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE c.session_id in (select session_id from sys.dm_exec_requests where database_id not in (‘1′,’4’) )) >0
begin
set @tableHTML =
‘<div><b>user report</b></div><br>’ + — This is the bold text at the top of your email.
‘<table border=”1″ cellpadding=”5″><font face=”Calibri” size=2>’ +
‘<tr><th>’ + @columnHeaders + ‘</th></tr>’ +
convert(nvarchar(max),
(
SELECT
td = c.session_id,”,
td = DB_NAME(database_id),”,
td = s.host_name,”,
td = s.program_name,”,
td = s.client_interface_name,”,
td = s.login_name,”,
td = s.nt_domain,”,
td = s.nt_user_name,”,
td = c.connect_time,”,
td = s.login_time,”
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE c.session_id in (select session_id from sys.dm_exec_requests where database_id not in (‘1′,’4’) ) )
for xml path(‘tr’), type
)) +
–‘</font>’
‘</table>’
exec msdb.dbo.sp_send_dbmail
@recipients = ‘@mail.com;’, — Email address of recipient
@body = @tableHTML,
@subject = @emailSubject,
@body_format = ‘HTML’
end
else
begin
exec msdb.dbo.sp_send_dbmail
@recipients = ‘@mail.com;’, — Email address of recipient
@body = ‘0 rows effected’,
@subject = @emailSubject,
@body_format = ‘HTML’
END
selectstarfromyogi 