任务执行失败，因为在作业中使用sp_send_dbmail发送邮件并将查询结果附加为文件。

Question

任务执行失败，因为在作业中使用sp_send_dbmail发送邮件并将查询结果附加为文件。

sql-serversql-server-2008attachmentsp-send-dbmail

19

我遇到了以下问题：尝试使用sp_send_dbmail通过执行普通查询将查询结果作为文件附件发送电子邮件时，一切似乎正常工作。但是，如果将相同的代码添加到JobStep并运行作业，则失败。

作业历史记录中的错误提示如下：

格式化查询时出错，可能由于无效参数[SQLSTATE 42000]（错误22050）。该步骤失败。

但是，当我注释掉引用文件附件的参数时，它又开始正常工作了。

exec msdb.dbo.sp_send_dbmail 
    @profile_name = 'profile_name', 
    @recipients  = 'some@mail.com',
    @body = 'body',
    @subject = 'subj',
    --Parameters that refers to attached file
    @attach_query_result_as_file = 1, 
    @query_result_header = 0,
    @query_result_no_padding = 1,
    @query = 'select 1',
    @query_attachment_filename = 'test.csv'

有什么建议吗？

- Paul Kyrejto

此外，即使我将这段代码封装成存储过程，最终结果仍然相同。 - Paul Kyrejto

所以一切都正常 - 你只是不能指定返回结果的名称？你尝试过不同的名称吗？ - Jimbo

8个回答

9

我曾遇到类似的问题。我使用的是SQL Server 2008 R2。通过添加选项，我收到了邮件并获取了更多有关错误的信息：

@append_query_error = 1,

我收到了一封错误邮件，指出我的权限不足，而不是我的查询结果：

   Msg 916, Level 14, State 1, Server SERVER\INST01, 
Procedure GetSalesReport, Line 62
The server principal "CONTROLLEDNETWO\sql.service" is not able 
to access the database "MYDB01" under the current security co
ntext.

我的查询试图访问一些SQL Agent没有权限（实际上在我的情况下，它甚至无法访问这些表）的表。

我通过SQLSMS解决了这个问题，通过向数据库"MYDB01"添加一个名为"CONTROLLEDNETWO\sql.service"的新用户并授予"select"权限。

- Raf

2

非常感谢您提供的帮助。我想分享一下我尝试使用Excel（xls）附件所做的事情，即将结果放在列中。通过添加query_result_no_padding = 1和query_result_separator= ' , '（这是一个制表符，在引号内输入两个制表符）来实现了这一点。

@query_result_header= 1,
@attach_query_result_as_file = 1,
@query_result_no_padding = 1,
@query_attachment_filename = 'TestPriceFlingerReport.xls',
@query_result_separator= '  ,   ',
@profile_name = 'Test Exchange Server'

- Dan Pullman

1

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Main Profile',
    @recipients = 'me@vwp.com',
    @subject = 'Test',
    @body = 'this is a test',
    @execute_query_database = 'myTargetDatabase_mscrm',
    @query = N'SELECT * from myTargetDatabase_mscrm.dbo.SystemUserBase',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'Test.txt'

作为参考，这个问题一直以来都被显示为由域管理员调用，但实际运行时是使用本地\sqladmin身份。在尝试打开和关闭变量并授权后，我发现作业脚本仍在使用主数据库。我发现设置就在眼前。它在步骤的配置中。我将其更改为msdb，然后它就可以正常工作了。请记住，我根据一些帖子将选择从myTable更改为从myDatabase.dbo.myTable进行了更改。这可能有或没有有助于解决问题。我还使用了@execute_query_database来确保它从正确的位置运行查询。同样，这可能不是必要的。无论最终是什么让它满意，它与是否连接无关。

- Eric Hula

1

在我的情况下，它无法识别表属于哪个数据库。一旦将database.dbo.table添加到查询中，它就可以工作了。

- SQLguest

0

当您手动执行查询时，会使用“您”的凭据。当 SQL 代理执行相同的查询时，将使用 SQL 代理服务帐户的凭据。默认情况下，SQL Server 代理将使用 LocalSystem 帐户凭据。解决问题的一种方法是将运行 SQL Server 代理服务的用户更改为具有访问 csv 目录/文件权限的用户。

- David Brabant

没有任何文件路径。文件是在执行被 sp_send_dbmail 接受的查询时动态生成的，并且用其结果填充。 - Paul Kyrejto

我真的闻到了权限问题。您是否已经检查过您的用户和SQL Server代理帐户在数据库对象上具有相同的权限？ - David Brabant

0

我认为这个问题是由于SQL 2008及更高版本中实施的有关仅限于sp_send_dbmail的安全锁定更改所致。只有在将qry传递给send_dbmail以执行并通过电子邮件返回结果时才会发生此问题。问题在于错误消息具有误导性且不合适。一个好的解决方案是创建一个SQL用户，该用户仅具有执行该查询所需的最低权限。例如，db_reader、db_writer和db_owner（如果绝对必要）。并使该用户成为所有者。您还可以创建一个SQL凭据，并配置该SQL作业在该SQL凭据下运行。

- user2238156

0

我也遇到了这个问题，并通过这里的建议分两部分解决了它。

1）右键单击作业上的“查看历史记录”，显示失败详细信息，失败通知给出了作业运行的用户名称，因此我为该用户提供了只读访问权限。

2）我忘记指定 DBName.dbo.MyTableName，而是仅使用了 MyTableName。

顺便说一下，所有的电子邮件都被发送到了我的垃圾邮件文件夹中。

- Resource

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Paul Kyrejto · Accepted Answer

我已经找到了解决这个问题的方法。不知道为什么它会起作用，但无论如何。 :) 这肯定与安全有关。

我调查了SQL代理正在以域用户的名义运行，比如说DOMAIN\User。它在服务器上拥有完整的管理员权限（sysadmin服务器角色等）。 SQL Server本身也在同一用户下运行。

包含对sp_send_dbmail的调用的作业步骤在同一个DOMAIN\User下运行。

此外，我跟踪了当运行sp_send_dbmail的查询部分时，它尝试执行exec xp_logininfo 'DOMAIN\User'来检查该用户是否OK。令人惊讶的是：某些东西明显不对。这个检查以以下方式结束:

Msg 15404, Level 16, State 19, Server SQLC002INS02\SQLC002INS02, Line 1
Could not obtain information about Windows NT group/user 'DOMAIN\User.', error code 0x2.

这可能意味着用户的密码已过期、用户被锁定或其他对该用户不利的事情。

我决定更改代理用户存在风险。所以我想到了以“sa”的名义发送邮件，该用户具有相同的“sysadmin”服务器角色但具有SQL授权并省略此AD检查步骤。

看起来像是一个用户假装成管理员要求真正的管理员为他运行危险代码 :)

因此，这个作业的最终代码的第一步也是唯一一步如下:

execute as login = 'sa'
exec msdb.dbo.sp_send_dbmail 
    @profile_name = 'profile_name', 
    @recipients  = 'some@mail.com',
    @body = 'body',
    @subject = 'subj',
    --Parameters that refers to attached file
    @attach_query_result_as_file = 1, 
    @query_result_header = 0,
    @query_result_no_padding = 1,
    @query = 'select 1',
    @query_attachment_filename = 'test.csv'
revert