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

Question

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)

Answer 1

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