How to add multiple query results into one csv file?

Question

By the help of the members of this community I have created a script which shows recent T-log backup for each database in the server. Instead of sp_MSforeachdb I used a customized script written by Aaron Bertrand.

Here is my actual command which gets recent LOG backups for each database in the server:

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;'

Now, the problem is I want to send all these query results into email in CSV file but I am not able to paste all these results. I tried creating a new table and storing all query results there, unfortunately I got an error. I need an alternative solution.

I would be glad to hear your ideas.

Answer 1

I started by extracting the SELECT query into a separate SSMS window. I replaced double-ticks (quotes) with single-ticks and added into dbo.TableForEmailingLogBackups that would allow me to create a real table to hold all of the results.

Note: I had to change a part of your main query from WHERE sysdb.name = DB_NAME() to AND sysdb.name = DB_NAME() to get the query to work. This is what that looked like:

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;

---

Then, I used the following code to truncate dbo.TableForEmailingLogBackups, insert the results of the stored procedure call EXEC master.dbo.sp_ineachdb and send the email. Note that you'll need to change the email part to send it to the appropriate email address

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 = '[email protected];',
    @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 = ','

---

A minute later, I received the email with the attachment.