SQLTeam.com | Weblogs | Forums

How Can I automate query result and send email in excel sheet which will be running every monday with out using ssis and ssrs?

sql2008r2

#1

DECLARE @Delimiter Char(1)

SET @Delimiter = CHAR(9)

EXEC MSDB.dbo.sp_Send_DBMail
@Recipients='faiyazu99@gmail.com',
@Subject='Some Audit',
@Body='Attached is some audit information.',
@query = 'SELECT * FROM PTEST.DBO.CUSTOMER WHERE MASTER_CUSTOMER_ID LIKE ''000210232''
' ,
@Attach_Query_Result_As_File = 1,
@Query_Result_Header = 1,
@Query_Attachment_Filename = 'sample.csv',
@Query_Result_Separator = @Delimiter

This is small query result I tried to send meail in excel sheet but file is corrupted and data is not in good format.??

Can anyone help me out .


#2

As far as the scheduling goes, you could put your code in a SQL Agent Job and set it to run weekly on Monday. How to put the query into Excel is a different story. How you considered using an Excel driver and "insert"ing the data? You could find the driver with a Google search.


#3

First - you cannot send an actual Excel file using sp_send_dbmail. What you can send is a CSV or tab-delimited text file that Excel can open. The CSV file is the better option...and your problem is that you cannot open the file directly from email - you have to save the file and then open it.

To fix that issue - you have to tell Excel this is a CSV file to be opened and you do that by defining the separator in the first column of data. Here is an example:

Declare @sqlCommand nvarchar(max)
      , @recipients varchar(max) = 'faiyazu99@gmail.com'
      , @cc_recipients varchar(max) = ''
      , @bcc_recipients varchar(max) = '';

Declare @body nvarchar(max) = 'Attached is some audit information';

    Set @sqlCommand = '
    Set Nocount On;
 Select c.FirstColumn As [Sep=,' + char(13) + char(10) + 'FirstColumnName]
      , {list out all columns to be included}
   From dbo.Customer c
  Where c.master_customer_id = ''000210232'';'

Execute msdb.dbo.sp_send_dbmail
        @profile_name = '{Your Database Mail Profile'
      , @reply_to = '{your-reply@address.com}'
      , @from_address = '{your-from@address.com}'
      , @recipients = @recipients
      , @copy_recipients = @cc_recipients
      , @blind_copy_recipients = @bcc_recipients
      , @subject = 'Some Audit'
      , @body = @body
      , @query = @sqlCommand
      , @query_result_separator = ','
      , @query_result_header = 1
      , @query_result_width = 8000
      , @attach_query_result_as_file = 1
      , @execute_query_database = 'PTEST'
      , @query_attachment_filename = 'SomeAudit.csv';

This should give you a good start...


#4

Thanks