通过邮件发送带有SQL Server作业的查询结果(Send by mail a query result with a job in SQL Server)

通过邮件发送带有SQL Server作业的查询结果(Send by mail a query result with a job in SQL Server)

我正在尝试通过SQL Server作业发送带有查询结果的电子邮件。

查询工作正常，当我在sp_send_dbmail的@query参数中传递TABLE时，我遇到了一个问题

这是我的代码：

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

我收到了这个错误（用法语，但如果需要可以轻松翻译）：

第0行：过程：sp_send_dbmail，消息206，级别16，状态2：Conflit de types d'opérandes：table est不兼容avec nvarchar（max）

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)

最满意答案

我用这段代码解决了我的问题：

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

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