Execute Sql statement and send result to email

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:

• execute select statement

-
send result:

Use MSDB
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'abc',
        @recipients = 'recipients@company.com',
        @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 = 'recipients@company.com',
@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 =

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 = 'yourfriend@Adventure-Works.com',  
    @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 = 'recipients@company.com'
    , @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.

StackExchange Database Administrators Q#168395, answer score: 13