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 = 'foo@foooo.com', 
      @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)

According to MSDN, `[ @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.` — SelvaS, Apr 15 '15 at 08:40
I want to set the result of the query in the parameter 'query' and in this example I pass the variable 'res' to 'query' — Xavier W., Apr 15 '15 at 08:42
Try something like this, `@query = 'SELECT SiteCode ,MAX(DateODV) AS DateLastODV FROM Configuration.ODVCompteur where year(DateODV) = 2015 group by SiteCode order by DateLastODV desc'` — SelvaS, Apr 15 '15 at 08:43
I already tried this but got an error like that : `Error formatting query, probably invalid parameters [SQLSTATE 42000] (erreur 22050).` — Xavier W., Apr 15 '15 at 08:49

score 3 · Accepted Answer · answered Apr 16 '15 at 10:39

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 = 'foooo@foo.com', 
      @subject = @mailSubject,
      @query = @STMT,      
      @Attach_Query_result_as_file = 0

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

mxix · Answer 2 · 2015-04-15T08:58:27.517

1

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

edited Apr 15 '15 at 08:58

answered Apr 15 '15 at 08:42

mxix

3,539
1
16
23

Of course I already tried this but got an error like that : `Error formatting query, probably invalid parameters [SQLSTATE 42000] (erreur 22050).` The query works perfectly in the other case – Xavier W. Apr 15 '15 at 08:47
1

you should check what credentials are being supplied when running the Job. You should look at this question: http://stackoverflow.com/questions/15112849/sp-send-dbmail-executed-from-job-fails-with-query-result-attached-as-file – mxix Apr 15 '15 at 08:53

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

2 Answers2