Identify Helpful Information about Server in Email

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identify Helpful Information about Server in Email

Author

Topic

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2009-03-03 : 20:45:35

Hi

I have just started using the Email feature built into SQL 2008 and think its great. I have created a stored proc that will email me when certain things happen in my TSQL, just so I am aware if anything odd is happening. In my email stored proc I would like to attain as much information as what was going on at the server at the time of error. I have the below in my email which tells me the time, server and database that the error occured in. However are there any other useful bits of information I can get out of SQL Server which might help me.

Is there a parameter that I can call that tells me the CPU or memory usage of the server, at the time of sending the email.

declare @ServerInfo varchar(5000)
set @ServerInfo = '<br />Server Info'
set @ServerInfo = @ServerInfo + '<br />Date Sent:' + convert(varchar(100),getdate())
set @ServerInfo = @ServerInfo + '<br />Database:' + (SELECT db_name())
set @ServerInfo = @ServerInfo + '<br />Server: ' + @@servername

Also if there are any other nice bits of information I can collect would be much appreciated.

Many thanks in advance

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2009-03-03 : 22:08:59

I just noticed that you can view all emails sent via

select * from sysmail_sentitems

This tables has lots of additional fields such as:

query
execute_query_database
query_result_width
query_result_seperator

But im not sure how I populate these fields or attain such information from SQL Server, I have included below how I am sending the email.

EXEC msdb.dbo.sp_send_dbmail
@recipients = @EmailTo,
@Subject = @prmSubject,
@Body = @EmailBody,
@body_format = 'HTML';

Any help would be very helpful

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources