Shulou(Shulou.com)05/31 Report--

This issue of content which Xiaobian will bring you about how to achieve SQL SERVER 2005 mail function, the article is rich in content and from a professional point of view for everyone analysis and description, read this article I hope you can have some harvest.

As an administrator, if the results of daily monitoring can be used in a way to remind you, this can avoid always remotely connecting to SQL SERVER to run an operation every once in a while, and then see the results. SQL SERVER 2005 comes with the mail function can achieve this requirement oh.

The database mail is configured. Below we use T-SQL statement to achieve mail sending, this time using a stored procedure msdb.dbo.sp_send_dbmail. See MSDN for the specific usage of this stored procedure.

Use the following statement to view the top 50 operations in the cache with average operation times.

Exec msdb.dbo.sp_send_dbmail

@profile_name ='SQLSERVEREMAIL 1', --Database mail profile used

@recipients='huangzhf@fabao.cn', --recipient address, multiple recipients can be separated by semicolons

@subject='50 longest statements in cache per hour', --Message subject

@query='SELECT TOP 50

total_worker_time/execution_count/1000 AS [Avg CPU Time(ms)],deqs.execution_count,

(SELECT SUBSTRING(text,statement_start_offset/2+1,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2+1) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text

FROM sys.dm_exec_query_stats deqs

ORDER BY [CPU Time(ms)] DESC', --Specific queries T-SQL

@attach_query_result_as_file = 1, --Query results are sent as attachments

@query_no_truncate = 1 --Do not truncate long field values

Make the above T-SQL a scheduled task and set it to execute once per hour.

This way, you can receive the required information in your mailbox every hour.

The above is how to realize SQL SERVER 2005 mail function shared by Xiaobian for everyone. If there is a similar doubt, please refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.