CODEWITHSUNDEEP
sqlsql-serveremailjobs
Xavier W.
Xavier W.

Reputation: 1360

Send by mail a query result with a job in SQL Server

I'm trying to send an email by a SQL Server job with the result of a query.

The query works perfectly and I face an issue when I pass a TABLE in the @query parameter of sp_send_dbmail

Here is my code : 

DECLARE @res TABLE 
(
  SiteCode [nvarchar](50), 
  DateLastODV [datetime]
);

INSERT INTO @res
SELECT
      SiteCode
      ,MAX(DateODV) AS DateLastODV
  FROM Configuration.ODVCompteur
  where year(DateODV) = 2015
  group by SiteCode
  order by DateLastODV desc

EXEC  msdb.dbo.sp_send_dbmail
      @profile_name = 'Foo',
      @recipients = '[email protected]', 
      @subject = 'Foooooooo',
      @query = @res,      
      @Attach_Query_result_as_file = 0

I got this error (in french but can easily be translate if needed) :

Line 0: Procedure: sp_send_dbmail, Msg 206, Level 16, State 2: Conflit de types d'opérandes : table est incompatible avec nvarchar(max)

Upvotes: 1

Views: 29857

Answers (2)

Xavier W.
Xavier W.

Reputation: 1360

I solved my problem using this code : 

DECLARE @count TABLE(SiteCode [nvarchar](50), DateLastODV [datetime])

DECLARE @Separateur varchar(1)
DECLARE @bodyHtml NVARCHAR(MAX)
DECLARE @mailSubject NVARCHAR(MAX)
DECLARE @STMT VARCHAR(100)
DECLARE @RtnCode INT

SET @Separateur=';'

INSERT INTO @count
SELECT
      SiteCode
      ,MAX(DateODV) AS DateLastODV
  FROM Configuration.ODVCompteur
  where year(DateODV) = 2015
  group by SiteCode
  order by DateLastODV DESC

BEGIN
    IF OBJECT_ID('tempdb..##TEMPTABLE') IS NOT NULL
    drop table ##TEMPTABLE

    select * into ##TEMPTABLE FROM @count 

    SET @STMT = 'SELECT * FROM ##TEMPTABLE'
    SET @bodyHTML ='Test ODV'
    SET @mailSubject ='Supervision ODV'

    USE msdb

    EXEC  @RtnCode = sp_send_dbmail
      @profile_name = 'Fooo',
      @query_result_separator=@Separateur,
      @recipients = '[email protected]', 
      @subject = @mailSubject,
      @query = @STMT,      
      @Attach_Query_result_as_file = 0

    IF @RtnCode <> 0
      RAISERROR('Error.', 16, 1)
END

Upvotes: 3

mxix
mxix

Reputation: 3659

According to the documentation on msdn of sp_send_dbmail

@query parameter expects a nvarchar(max) type not a Table.

replace 

@query = @res

with 

@query = 'SELECT
      SiteCode
      ,MAX(DateODV) AS DateLastODV
  FROM Configuration.ODVCompteur
  where year(DateODV) = 2015
  group by SiteCode
  order by DateLastODV desc'

EDIT:

While running this as a Job make sure the credentials being used by the SQL Agent service account have enough privileges to execute such query.

For testing purposes try executing as another login with execute as

Upvotes: 1

Related Questions