☰

为什么 sp_send_dbmail 在 SQL Agent 作业中失败？

tag-icon • tag-icon sql-server sql-server-2008
为什么 sp_send_dbmail 在 SQL Agent 作业中失败？

我有一个简单的查询，当帐户被禁用超过 30 天时，我会运行该查询来向我们的 AD 帐户管理员发送电子邮件通知。当我以 SA 身份登录并自行运行该查询时，它运行良好，但在 SQL Server 代理作业中运行时，它会失败。

以下是查询，其中替换了业务特定的项目和对象名称：

DECLARE @QueryString varchar(max)
SET @QueryString = 'Select TrackingTable.Username FROM dbName.Schema.TrackingTable inner join dbName.Schema.viewName on DisabledAccounts.username = viewName.username WHERE DATEDIFF(dd,DateDisabled,GETDATE()) > 25 AND viewName.OU = ''InactiveAccounts'''
EXEC msdb.dbo.sp_send_dbmail  @profile_name = 'Profile', @body = 'This is the body text. Nothing interesting here.
', @recipients = '[email protected]', @subject='Account status update', @query = @QueryString, @importance = 'High'

当我以 SA 身份运行时，消息会被发送。在 SQL Server 代理作业中，我收到此错误：

Executed as user: DOMAIN\MemberOfDomainAdmins. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050).  The step failed.

执行该作业的域用户被分配了 msdb 和用于附加到消息的查询的数据库上的 db_owner 角色。它在两个数据库上的默认架构都是 dbo。

它还被分配了服务器上的 sysadmin 角色，并且是 msdb 上 DatabaseMailuserRole 的成员。它还具有查询使用的数据库邮件配置文件的私有和公共访问权限。

我在网上看到过几十个相同问题的示例，但我已经按照示例中的步骤解决了这个问题。我还能尝试什么？

答案1

我今天早上才搞明白。问题出在DATEADD函数上。当它位于 EXECUTE 语句中时（SQL Server 代理作业将以此方式运行它），间隔必须是特定的日期部分（天），而不是其中一个标记（“dd”）。

因此，这个函数：DATEDIFF(dd,DateDisabled,GETDATE())

需要更像这样：DATEDIFF(day,DateDisabled,GETDATE())

那里有多次对该函数的调用，但你明白了。

以下是我解决问题的方法：我让 SQL Server Management Studio 在新的查询编辑器文档中将该作业编写为 CREATE 脚本。找到要运行的步骤后，我将其复制出来。主要内容如下：

@command = N'[my query]'

我将这组行复制到一个新窗口，并为命令变量添加了 DECLARE。

最后，我过去常常EXECUTE (@command) AS USER = '[the user the job runs as]'看看会发生什么。以这种方式运行查询，我得到了很多通过这种方式，我可以得到比作业日志中更详细的错误消息。

我现在已经改正了，工作运行正常。

相关内容