如何將多個查詢結果添加到一個 csv 文件中？

在這個社區成員的幫助下，我創建了一個腳本，顯示伺服器中每個數據庫的最近 T-log 備份。sp_MSforeachdb我沒有使用Aaron Bertrand 編寫的自定義腳本。

這是我的實際命令，它為伺服器中的每個數據庫獲取最近的 LOG 備份：

EXEC master.dbo.sp_ineachdb
@exclude_list = N'tempdb',
@command = 
'SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as ''Size Meg'' 
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig''
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN ''Yes'' ELSE ''No'' END AS isOlderThan24Hours
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate())) 
AND bkup.type = ''L''
WHERE sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;'

現在，問題是我想將所有這些查詢結果發送到 CSV 文件中的電子郵件中，但我無法粘貼所有這些結果。我嘗試創建一個新表並將所有查詢結果儲存在那裡，不幸的是我得到了一個錯誤。我需要一個替代解決方案。

我很高興聽到你的想法。

我首先將查詢提取SELECT到一個單獨的 SSMS 視窗中。我用單引號替換了雙引號（引號），並添加into dbo.TableForEmailingLogBackups了這將允許我創建一個真實的表格來保存所有結果。

注意：我必須將您的主要查詢的一部分從WHERE sysdb.name = DB_NAME()to更改AND sysdb.name = DB_NAME()為使查詢正常工作。這就是它的樣子：

SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as 'Size Meg' 
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig'
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + ' minutes, '
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN 'Yes' ELSE 'No' END AS isOlderThan24Hours
into dbo.TableForEmailingLogBackups
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate())) 
AND bkup.type = 'L'
and sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;

---

然後，我使用以下程式碼截斷dbo.TableForEmailingLogBackups，插入儲存過程呼叫的結果EXEC master.dbo.sp_ineachdb並發送電子郵件。請注意，您需要更改電子郵件部分以將其發送到適當的電子郵件地址

truncate table dbo.TableForEmailingLogBackups

insert into dbo.TableForEmailingLogBackups
EXEC master.dbo.sp_ineachdb
@exclude_list = N'tempdb',
@command = 
'SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as ''Size Meg'' 
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig''
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN ''Yes'' ELSE ''No'' END AS isOlderThan24Hours
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate())) 
AND bkup.type = ''L''
and sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;'

EXEC msdb.dbo.sp_send_dbmail
   @profile_name = 'SqlServerEmailProfile',
   @recipients = 'recipients@.test.com;',
   @subject = 'Transaction log backup report',
   @query = N'set nocount on;select * from dbo.TableForEmailingLogBackups',
   @attach_query_result_as_file = 1,
   @query_result_header= 1,
   @query_attachment_filename = 'test.csv',
   @importance = 'High',
   @query_result_no_padding = 1,
   @query_result_separator = ','

---

一分鐘後，我收到了帶有附件的電子郵件。

引用自：https://dba.stackexchange.com/questions/248525