Sending query results to Excel through e-mail in SQL Server

declare @body1 varchar(4000)
declare @body2 varchar(4000)
DECLARE @tab char(1) = CHAR(9)
DECLARE @CRLF char(2)
SELECT @CRLF=CHAR(13)+CHAR(10)

--@query_result_separator=

set @body1 = 'Please find the attached list. '

  +@CRLF+ N' Thanks, SQL DBA Team.'

EXEC msdb.dbo.sp_send_dbmail

    @profile_name='ajaymail',

    @recipients='security@sqlajay.com',
@blind_copy_recipients='dbateam@sqlajay.com',

    @subject = 'DBA database Monthly Report.',

    @body = @body1,

    @body_format = 'HTML',
             
    @query = 'Select USER_ID AS ID, NAME as Username, SECURITY as SecurityPermission, LST_LOGIN AS LastLogin from DBA..table_name',

    @query_result_header = 1,
@query_result_separator =@tab,

    @exclude_query_output = 1,

    @append_query_error = 1,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'DBA.csv',

    @query_result_no_padding = 1

              

No comments:

Post a Comment