Execute Sql statement and send result to email

Question

I have to execute SQL statement every day and send result to dba by email.I configured database email.

Below is query:

 SELECT
        account.accountID,
        account.name
    FROM
        account
        LEFT OUTER JOIN accountfeaturesetting afs 
            ON afs.accountid = account.accountid 
                and afs.featureid = 'Schedules' 
                and afs.settingid = 'EditReasons'
    WHERE
        ISNULL(afs.Value, '0') = '1'
        AND NOT EXISTS
 (SELECT 1 FROM program 
WHERE program.AccountID = account.AccountID
 AND program.Active = 1 AND 
(program.ScheduleEditReasonFlags <> 0 OR
 program.ScheduleEditReasonFields <> 0))
        AND account.IsMaster = 0

For this I created Sql job having 2 steps:

1. execute select statement

2. send result:

   Use MSDB
       EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'abc',
           @recipients = '[email protected]',
           @subject = 'queryresultset',
           @body='testmail',
           @query_attachment_filename='warning.csv'
   

It is throwing an error and the result set is not attached with query.

Error: Parameter @attach_query_result_as_file cannot be 1 (true) when no value is specified for parameter @query.
A query must be specified to attach the results of the query.
[SQLSTATE 42000] (Error 14625)

Edit:

Use MSDB
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'abc',
        @recipients = '[email protected]',
        @subject = 'queryresultset',
        @body='testmail',
        @query_attachment_filename='warning.csv'
    DECLARE @msg VARCHAR(250);
    declare @query varchar(2048);
    SELECT @msg = 'Please refer to the attached spread sheet for the report.';  

    set @query='
        SELECT
        account.accountID,
        account.name
    FROM
        account
        LEFT OUTER JOIN accountfeaturesetting afs 
            ON afs.accountid = account.accountid 
                and afs.featureid = 'Schedules' 
                and afs.settingid = 'EditReasons'
    WHERE
        ISNULL(afs.Value, '0') = '1'
        AND NOT EXISTS
 (SELECT 1 FROM program 
WHERE program.AccountID = account.AccountID
 AND program.Active = 1 AND 
(program.ScheduleEditReasonFlags <> 0 OR
 program.ScheduleEditReasonFields <> 0))
        AND account.IsMaster = 0
        go';
        exec (@query)

Above query is not taking schedules and editreasons value column. I am still getting a syntax error after declaring all variables like:

declare @featureid varchar(100);
declare @settingid varchar(100);
declare @value varchar(100);

Answer 1

Your code is backwards. You are sending the email, then generating the query results. The sp_send_dbmail stored procedure will run the query for you, and append the results either in the body of the email or as an attachment.

In case you aren't aware, Microsoft has extensive, comprehensive, and free documentation on their website for SQL Server. For instance, sp_send_dbmail is available in great detail at: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql

The @query parameter is documented as:

[ @query= ] 'query'
Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

They also have several examples of calling sp_send_dbmail, including this salient point:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Adventure Works Administrator',  
    @recipients = '[email protected]',  
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.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 ;  

So, you need to pass the query definition into msdb.dbo.sp_send_dbmail like this:

DECLARE @msg VARCHAR(250);
DECLARE @query varchar(2048);

SET @msg = 'Please refer to the attached spread sheet for the report.';  

SET @query='
    SELECT
        account.accountID,
        account.name
    FROM
        dbo.account
        LEFT OUTER JOIN dbo.accountfeaturesetting afs 
            ON afs.accountid = account.accountid 
                AND afs.featureid = ''Schedules'' 
                AND afs.settingid = ''EditReasons''
    WHERE
        ISNULL(afs.Value, ''0'') = ''1''
        AND NOT EXISTS 
            (
                SELECT 1 
                FROM dbo.program 
                WHERE program.AccountID = account.AccountID
                    AND program.Active = 1 
                    AND (
                        program.ScheduleEditReasonFlags <> 0 
                        OR program.ScheduleEditReasonFields <> 0
                        )
            )
        AND account.IsMaster = 0;';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'abc'
    , @recipients = '[email protected]'
    , @subject = 'queryresultset'
    , @body= @msg
    , @body_format = 'TEXT'
    , @query = @query
    , @execute_query_database = 'MyDB'  
    , @attach_query_result_as_file = 1
    , @query_attachment_filename='warning.csv'
    , @query_result_header = 1
    , @query_result_width = 80
    , @query_result_separator = ' '
    , @exclude_query_output = 0
    , @append_query_error = 1
    , @query_no_truncate = 0
    , @query_result_no_padding = 0;

Also note the extra parameters included, which control various output factors such as how the query is formatted etc.

Note in the @query text above, I've "escaped" the single quotes to prevent SQL Server from considering them as literal delimiters. Essentially, any single-quotes appearing inside the @query parameter need to be turned into two single-quotes. Erland Sommarskog has an excellent piece on Dynamic SQL and nested strings.

Answer 2

I believe you should be doing something similar to this:

DECLARE @sub VARCHAR(100);
DECLARE @qry VARCHAR(1000);
DECLARE @msg VARCHAR(250);
DECLARE @query NVARCHAR(1000);
DECLARE @query_attachment_filename NVARCHAR(520);
SELECT @sub = 'TEST XML ATTACHMENT';
SELECT @msg = 'Please refer to the attached spread sheet for the report.';
SELECT @query = 'SET NOCOUNT ON;
            Select top 10 * from master..sysobjects WITH(NOLOCK)';
SELECT @query_attachment_filename = 'test.csv';
EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'SQLMAIL',
     @recipients = '[email protected]',
     @copy_recipients = '[email protected]',
     @body = @msg,
     @subject = @sub,
     @query = @query,
     @query_attachment_filename = @query_attachment_filename,
     @attach_query_result_as_file = 1,
     @query_result_header = 1,
     @query_result_width = 256,
     @query_result_separator = '   ',
     @query_result_no_padding = 1;

ref: http://sqlworkday.blogspot.ru/2010/12/how-to-send-csv-or-xls-file-as.html

Answer 3

DECLARE @Account_SQL_QUERY NVARCHAR(MAX)
DECLARE @Account_FILE_NAME varchar(MAX)
SET @Account_FILE_NAME='Account_RejectedRecords_'+Replace(Replace(replace(Substring(convert(varchar, getdate(),120),1,16),'-',''),' ',''),':','')+'.csv'  

                IF OBJECT_ID('tempdb..##RejectedAccountRecord') is not NULL
                BEGIN 
                    Drop table ##RejectedAccountRecord
                END
                Select *  Into ##RejectedAccountRecord
                from [blk].[account_reconciliation_rejected] Where loaddate=convert(varchar, getdate(), 23)

SELECT @sub=Sub,@to=Email_to,@cc=Email_cc,@bcc=Email_bcc,@body=Email_body FROM dbo.EmailConfiguration 
                WHERE project = 'Blackline' and report_id = 'B1'

        IF (Select Count(*) from ##RejectedAccountRecord) >0
        BEGIN
            SET @Account_SQL_QUERY='Select * from ##RejectedAccountRecord'
            SET NOCOUNT ON

                EXEC msdb.dbo.sp_send_dbmail 
                        @profile_name           = 'AjeetDBMail'
                       ,@recipients             = @to
                       ,@copy_recipients        = @cc
                       ,@Query                  =@Account_SQL_QUERY
                       ,@subject                = @sub
                       ,@body                   = @HTMLBody
                       ,@body_format            ='HTML'
                       ,@query_attachment_filename =@Account_FILE_NAME
                       ,@attach_query_result_as_file = 1
                       ,@query_result_separator='   '
                       ,@query_result_width =10000
                       ,@query_result_no_padding=1