MS Excel file with sp_send_dbmail

• sqlqueue

  SSC Eights!

  Points: 922

  More actions

  May 4, 2009 at 12:57 pm

  #133772

  I was using sp_send_dbmail to send the query result to recipients like below

  Ex:

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'AdventureWorks Administrator',

  @recipients = 'danw@Adventure-Works.com',

  @query = 'SELECT COUNT(*) FROM AdventureWorks.Production.WorkOrder

  WHERE DueDate > ''2004-04-30''

  AND DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
  @subject = 'Work Order Count',
  @attach_query_result_as_file = 1

  but now the requirement is to send the results in MS Excel file , can we use above funtionality to send the result with excel format?

  thanks for your time on this!!

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  May 19, 2009 at 3:04 pm

  #997262

  Check [ @file_attachments = ] 'file_attachments' parameter of sp_send_dbmail in BOL.

  MJ

• poratips

  Hall of Fame

  Points: 3994

  More actions

  May 21, 2009 at 1:31 pm

  #998527

  I am not 100% sure but you might need to MS Office tools as it will create the fiel on your server not your local m/c.

  Or You might need to create excel fiel with column header (exact name) and need to do maintannce each time it will throw an error if you don't rename or move it.

  Please check BOL.

• TRACEY-320982

  SSChampion

  Points: 13653

  More actions

  May 21, 2009 at 2:05 pm

  #998552

  Here something i use it sends email but makes it look pretty like excel.

  DECLARE @Emailrecipients varchar(MAX)

  SET ANSI_NULLS ON

  SET QUOTED_IDENTIFIER OFF

  SET @Emailrecipients = 'someone@x.com'

  DECLARE @tableHTML nvarchar(MAX) ;

  SET @tableHTML =

  N'TD{font-family: calibri; font-size: 10pt;}' +

  N'

  This report contains DB DRIVE SPACE USAGE

  ' +

  N'

  ' +

  N'

  '

  +

  CAST ( ( SELECT td = SERVERNAME,'',

  TD = DRIVE, '',

  TD = FREE_MB, '',

  TD = TOTAL_MB, '',

  TD = FREE_PERCENTAGE,'',

  td = CONVERT(char(26),STAT_DATE,109)

  FROM MYTABLE

  FOR XML PATH('tr'), TYPE

  ) AS NVARCHAR(MAX) ) +

  N'

  SERVERNAME	DRIVE	FREE_MB	TOTAL_MB	FREE_PERCENTAGE	STAT_DATE

  ' ;

  --print @TABLEHTML

  EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Name you got set on SQL Database mail',

  @recipients = @Emailrecipients,

  @subject = @EmailSubject,

  @body = @tableHTML,

  @body_format = 'HTML' ;

  For attachement your use above.....that someone mentioned.