CODEWITHSUNDEEP
sql-servert-sql
LT268
LT268

Reputation: 135

SQL query results to mail- Receiving Error formatting query, probably invalid parameters error

I am trying to send the invalid codes to mail. However when I execute the job for stored procedure, it completed fine and did not send any email. When I execute the query alone, I am getting the results. Did I miss any configuration setup?

CREATE PROCEDURE sp_Send_Email_Invalid_code 
    @MONTH AS int = 0
AS
BEGIN
    DECLARE @Email_Subject AS nvarchar(250) = 'Codes Missing in master table';
    DECLARE @Email_Receipients_To AS nvarchar(max) = 'xxx.yyy.com';
    DECLARE @IsUseEmailConfig AS bit = 1;
    DECLARE @Email_Category AS nvarchar(250);

    SET @IsUseEmailConfig = 0
    SET @Email_Category = 'Codes Missing in master table'

    SELECT DISTINCT CODE 
    INTO #temp_1
    FROM tblRegCode nolock

    SELECT
        *
    FROM 
        #temp_1

    DECLARE @Query nvarchar(max);
    SET @Query = 'SELECT * FROM #temp_1';

    EXEC msdb.dbo.sp_send_dbmail 
              @profile_name = 'psmEmailer',  
              @recipients = 'xxx.yyy.com',
              @query = @Query,  
              @subject = 'Missing codes', 
              @attach_query_result_as_file = 1 ;

    DROP TABLE #temp_1;
END

My SQL Server agent job is

 DECLARE @MONTH AS int = 0
 DECLARE @dt datetime = GETDATE()

 SET @MONTH = DATEPART(mm, DATEADD(mm, -1, @dt))

 EXEC sp_Send_Email_Invalid_code @MONTH

Upvotes: 1

Views: 522

Answers (2)

Paul Andrew
Paul Andrew

Reputation: 3253

This is difficult to answer because there isn't anything obvious in the code above that is coursing the issue. Therefore the problem must be else where. My thoughts are 1) your query result set isn't returning anything. 2) Your email config is correct meaning the email can't be sent.

For number 1, we can't resolve this without checking your data, so moving on.

For number 2, try sending a simple email from the SQL instance using the send mail procedure in isolation. Like this:

EXEC msdb.dbo.sp_send_dbmail 
    @recipients = '[email protected]',
    @subject = 'Send Mail Test',
    @body = 'Testing',
    @body_format = 'HTML';

Then is you don't receive an email check the failure logs in MSDB with the following query.

USE [msdb]
GO

SELECT 
    l.[description] AS 'Error failure',
    f.[recipients],
    f.[subject],
    f.[sent_date],
    f.[body],
    f.[body_format]
FROM 
    [dbo].sysmail_faileditems f
    JOIN [dbo].sysmail_event_log l
        ON f.[mailitem_id] = l.[mailitem_id]
ORDER BY
    f.[sent_date] DESC

If you do receive the test email I'd suggest the problem is with your query results that your trying to attach.

Just another thought. You may also need to use the param @execute_query_database and @query_attachment_filename.

Check https://msdn.microsoft.com/en-us/library/ms190307.aspx for details.

Upvotes: 1

gofr1
gofr1

Reputation: 15977

So, you can make from your SP just this:

CREATE PROCEDURE sp_Send_Email_Invalid_code 
    @MONTH AS int 
AS
BEGIN

    DECLARE @Query nvarchar(max) = 'SELECT [CODE ] FROM tblRegCode nolock';

    EXEC msdb.dbo.sp_send_dbmail 
                            @profile_name = 'psmEmailer',  
                            @recipients = 'xxx.yyy.com',
                            @query = @Query,  
                            @subject = 'Codes Missing in master table', 
                            @attach_query_result_as_file = 1 ;

END
GO

In the SP you are not using @MONTH, why?

Try to add WITH EXECUTE AS OWNER in your SP, and run it via job.

Upvotes: 1

Related Questions