Emailing a Backup Report

I have this query which will email a CSV file of backup info:

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'test@test.com',@body='Message Body',
@subject ='Message Subject',@profile_name ='profile_name',
@query ='SELECT
               CONVERT(CHAR(100), SERVERPROPERTY(''Servername'')) AS Server,
               msdb.dbo.backupset.database_name,
               msdb.dbo.backupset.backup_start_date,
               msdb.dbo.backupset.backup_finish_date,
               msdb.dbo.backupset.expiration_date,
               CASE msdb..backupset.type
                     WHEN ''D'' THEN ''Database''
                     WHEN ''L'' THEN ''Log''
                     WHEN ''I'' THEN ''DIFF''
               END AS backup_type,
               msdb.dbo.backupset.backup_size,
               msdb.dbo.backupmediafamily.logical_device_name,
               msdb.dbo.backupmediafamily.physical_device_name,  
               msdb.dbo.backupset.name AS backupset_name,
               msdb.dbo.backupset.description
            FROM   msdb.dbo.backupmediafamily
               INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
            WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
            ORDER BY
               msdb.dbo.backupset.database_name,
               msdb.dbo.backupset.backup_finish_date',
@query_result_width = 900,
@attach_query_result_as_file = 1,@query_attachment_filename ='Results.csv'

The issue is that the CSV file is not easy to read. Some columns have large gaps and some have no gaps. Also the headers do not line up. How can I produce a nice looking table?
GeodynamicsAsked:
Who is Participating?
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
Please format Source appropriate for CSV file
0
 
Daniel_PLDB Expert/ArchitectCommented:
Have you tried @query_result_no_padding parameter? You also have @query_result_width but you can't use both at once.

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'test@test.com',@body='Message Body',
@subject ='Message Subject',@profile_name ='profile_name',
@query ='SELECT
               CONVERT(CHAR(100), SERVERPROPERTY(''Servername'')) AS Server,
               msdb.dbo.backupset.database_name,
               msdb.dbo.backupset.backup_start_date,
               msdb.dbo.backupset.backup_finish_date,
               msdb.dbo.backupset.expiration_date,
               CASE msdb..backupset.type
                     WHEN ''D'' THEN ''Database''
                     WHEN ''L'' THEN ''Log''
                     WHEN ''I'' THEN ''DIFF''
               END AS backup_type,
               msdb.dbo.backupset.backup_size,
               msdb.dbo.backupmediafamily.logical_device_name,
               msdb.dbo.backupmediafamily.physical_device_name,  
               msdb.dbo.backupset.name AS backupset_name,
               msdb.dbo.backupset.description
            FROM   msdb.dbo.backupmediafamily
               INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
            WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
            ORDER BY
               msdb.dbo.backupset.database_name,
               msdb.dbo.backupset.backup_finish_date',
@query_result_width = 900,
@attach_query_result_as_file = 1,@query_attachment_filename ='Results.csv',@query_result_no_padding=1

Open in new window

0
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
PatelAlpesh is right. SQL Server now formats csv file in Unicode so your data lands in one cell in csv file.
You could work this around by creating csv file using e.g. bcp and then send email by sp_send_db_mail attaching csv file created by bcp.
0
 
GeodynamicsAuthor Commented:
I've built the report in SSRS. Good suggestions though, I shall give you both 125 points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.