通过邮件发送带有 SQL Server 作业的查询结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29645387/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Send by mail a query result with a job in SQL Server
提问by Xavier W.
I'm trying to send an email by a SQL Server job with the result of a query.
我正在尝试通过 SQL Server 作业发送带有查询结果的电子邮件。
The query works perfectly and I face an issue when I pass a TABLE in the @queryparameter of sp_send_dbmail
查询完美运行，当我在@query参数中传递 TABLE 时遇到问题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)
第 0 行：过程：sp_send_dbmail，消息 206，级别 16，状态 2：Conflit de types d'opérandes?：table est incompatible avec nvarchar(max)
回答by Xavier W.
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
回答by mxix
According to the documentation on msdn of sp_send_dbmail
根据sp_send_dbmail 的msdn 上的文档
@query parameter expects a nvarchar(max) type not a Table.
@query 参数需要 nvarchar(max) 类型而不是表。
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.
将此作为作业运行时，请确保 SQL 代理服务帐户使用的凭据具有足够的权限来执行此类查询。
For testing purposes try executing as another login with execute as
出于测试目的，尝试使用execute as作为另一个登录 执行
