sql-server - 创建一个脚本，将两个或多个查询的结果组合起来，并使用 sp_send

问题描述

我想创建一个脚本，将两个或多个查询的结果结合起来，并使用 sp_send_dbmail 将其发送到电子邮件地址。

以下脚本捕获物理内存并使用 sp_send_dbmail 将结果通过电子邮件发送给收件人。结果以表格形式呈现。这个脚本工作正常。

 CREATE TABLE #Temp1
( 
  [total_physical_memory_mb] [varchar](128),
  [available_physical_memory_mb]  [varchar](128),
  [system_memory_state_desc] [varchar](128),
 )

INSERT INTO #Temp1
Select total_physical_memory_kb/1024 AS total_physical_memory_mb, available_physical_memory_kb/1024 AS available_physical_memory_mb, system_memory_state_desc from  sys.dm_os_sys_memory;

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT [total_physical_memory_mb] AS 'td','',[available_physical_memory_mb] AS 'td','', [system_memory_state_desc] AS 'td'
FROM #Temp1
--ORDER BY Rank 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3>SQL Physical Memory</H3>
<table border = 1> 
<tr>
<th> total_physical_memory_mb </th> <th> available_physical_memory_mb </th> <th> system_memory_state_desc </th> </tr>'    

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'PerformanceProfile', -- replace with your SQL Database Mail Profile 
@body = @body,
@body_format ='HTML',
@recipients = 'John.Smith@myemailaddress.com', -- replace with your email address
@subject = 'PERFORMANCE METRICS' ;

DROP TABLE #Temp1

结果显示在电子邮件中，如屏幕截图所示。

但是，我希望使用 sp_send_dbmail 将 2 个或更多查询的结果发送到电子邮件。目前，我将不得不创建单独的脚本。这将发送另一封电子邮件。例如：第二个脚本提供了Database File Size 和 Log File Size的结果。

 CREATE TABLE #Temp2
( 
  [Database_Name] [varchar](128),
  [Description]  [varchar](128),
  [Size_MB] [varchar](128),
  [Size_GB] [varchar](128)
 )

INSERT INTO #Temp2
Select db_name(database_id) as dbname,type_desc,(size * 8) /1024 as size_MB,(size * 8) /1024/1024 as size_GB from sys.master_files order by name;

DECLARE @xml_1 NVARCHAR(MAX)
DECLARE @body_1 NVARCHAR(MAX)

SET @xml = CAST(( SELECT [Database_Name] AS 'td','',[Description] AS 'td','', [Size_MB] AS 'td','',[Size_GB] AS 'td'
FROM #Temp2
--ORDER BY Rank 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3>Database File Size and Log File Size</H3>
<table border = 1> 
<tr>
<th> Database_Name </th> <th> Description </th> <th> Size_MB </th> <th> Size_GB </th> </tr>'    

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'PerformanceProfile', -- replace with your SQL Database Mail Profile 
@body = @body,
@body_format ='HTML',
@recipients = 'John.Smith@myemailaddress.com', -- replace with your email address
@subject = 'PERFORMANCE METRICS' ;

DROP TABLE #Temp2

我不知道如何组合这两个或多个查询，因此我只能发送一封包含所有结果的电子邮件。

标签： sql-serveremail

sql-server - 创建一个脚本，将两个或多个查询的结果组合起来，并使用 sp_send_dbmail 将其发送到电子邮件地址

问题描述

解决方案

推荐阅读