CODEWITHSUNDEEP
excelt-sqlexport-to-excelsql-jobleading-zero
Michael De Meersman
Michael De Meersman

Reputation: 21

Send xlsx formatted file as attachment using TSQL

For weeks I've been searching the answer for this problem I have. I need to send by email query results from the day before in an excel sheet.

Requirements are:

  1. Extract some data from database from the day before -- OK
  2. Send this query result as Excel file (format xlsx) by email -- NOK

Problems:

  1. Output is only in CSV even when I change the format in @query_attachment_filename. When I use xlsx the generated file cannot be opened.
  2. Missing leading zeros in first 2 columns

My half-working code now:

    use xxx

DECLARE @AWBPREFIX VARCHAR(255)
DECLARE @Query VARCHAR(MAX)
SET @AWBPREFIX = '[sep=,' + CHAR(13) + CHAR(10) + 'AWB.PREFIX]'
SET @Query =  ' 
SET NOCOUNT ON
SELECT AWB.PREFIX AS ' + @AWBPREFIX + ',
 AWB.SERIAL, 
AWB.ORIGIN, 
AWB.DEST, 
AWB_MOVE.CARRIER, 
AWB.PIECES, 
AWB.WEIGHT, 
AWB_MOVE.PIECES, 
AWB_MOVE.WEIGHT, 
AWB.NATURE_GOODS, 
AWB_MOVE.CARRIER, 
AWB_MOVE.FLIGHT_NUMBER, 
AWB_MOVE.FLIGHT_DATE, 
AWB_MOVE.ORIGIN, 
AWB_MOVE.DEST, 
AWB_MOVE.ULD_NUMBER, 
AWB_MOVE.SHC_LIST

FROM 
xxx..AWB 
INNER JOIN 
AWB_MOVE ON AWB.SEQ = AWB_MOVE.AWB_SEQ

GROUP BY 
AWB.PREFIX, 
AWB.SERIAL, 
AWB.ORIGIN, 
AWB.DEST, 
AWB_MOVE.CARRIER, 
AWB.PIECES, 
AWB.WEIGHT, 
AWB_MOVE.PIECES, 
AWB_MOVE.WEIGHT, 
AWB.NATURE_GOODS, 
AWB_MOVE.CARRIER, 
AWB_MOVE.FLIGHT_NUMBER, 
AWB_MOVE.FLIGHT_DATE, 
AWB_MOVE.ORIGIN, 
AWB_MOVE.DEST, 
AWB_MOVE.ULD_NUMBER, 
AWB_MOVE.SHC_LIST, 
AWB_MOVE.action_status

HAVING AWB_MOVE.FLIGHT_DATE = dateadd(dd,-1,cast(getdate() as date))  AND AWB_MOVE.DEST ="BRU" AND AWB_MOVE.action_status <>"NOT"'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Batch',
@recipients = 'xxx',
@execute_query_database = 'xxx',
@subject ='Import extracts',
@query =@Query,
@query_result_header = 1,
@query_result_separator=',',
@query_result_no_padding=1,
@query_result_width=32767,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'import_batch.csv'

I cannot use SSIS due to office restrictions.

Can someone help me please?

Thanks Michael

Upvotes: 1

Views: 8389

Answers (2)

Gabriel
Gabriel

Reputation: 1

I am able to create .XLS files as an attachment, but not .XLSX. I get a warning when opening it, but click "Yes" and it opens. Try that. There may be a better way that I have not found.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'My_SQL_MailProfile',
@recipients = '[email protected]',
@query = 'select * from My_Table' ,
@subject = 'Test email as XLS',
@attach_query_result_as_file = 1,
@query_attachment_filename='filename.xls',  --- XLS worked. XLSX shows as "corrupted"
@query_result_separator='   ' -- tab

Upvotes: -1

supergrady
supergrady

Reputation: 1322

sp_send_dbmail will not make a .xlsx file for you. However, you can use the method described here to make the .csv from sp_send_dbmail readable in excel.

If you have SSRS in your environment, you might be able to create a report subscription which publishes to excel.

Upvotes: 1

Related Questions