使用 sp_send_dbmail 发送附件时出现错误无法初始化 sqlcmd 库，错误号为 -2147467259 [英] Sending attachment with sp_send_dbmail getting error Failed to initialize sqlcmd library with error number -2147467259

问题描述

我正在尝试使用以下代码测试在 SQL 中发送附件.我收到错误 - 无法初始化 sqlcmd 库，错误号为 -2147467259.发送电子邮件工作正常，只有在添加 @query 时我才开始收到此错误.我错过了什么?

I am trying to test sending an attachment in SQL with the code below. I get an error -Failed to initialize sqlcmd library with error number -2147467259. Sending emails works just fine, only when adding the @query do I start getting this error. What am I missing?

DECLARE @query nvarchar(max) = 'select top (1) * from employees';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email',
@recipients = '[e-mail]',
@query = @query,                     
@query_attachment_filename = 'employees.csv',      
@body_format = 'HTML',
@subject = 'Test';

推荐答案

使用 @query 参数为 sp_send_dbmail 运行的查询在 msdb 的上下文中运行.这意味着对于您的查询，您实际上是在尝试引用对象 msdb.dbo.employees.该对象不太可能存在于 msdb 中，因为它是一个系统数据库(如果存在，我建议移动它)，并且由于该对象不存在，查询无法解析并且 sp_send_dbmail 生成错误.

Queries run using the @query parameter for sp_send_dbmail are run in the context of msdb. This means that for your query, you are effectively trying to reference an object msdb.dbo.employees. This object is unlikely to exist in msdb, as it is a system database (and if it does, I would suggest moving it), and as the object doesn't exist the query fails to parse and sp_send_dbmail generates an error.

要解决此问题，请在为 @query 参数定义的查询中使用 3 部分命名:

To fix the problem, use 3 part naming in the query you define for the @query parameter:

DECLARE @query nvarchar(MAX) = N'SELECT TOP (1) * FROM YOurDatabase.dbo.employees ORDER BY YourColumn;'; --Replace values as needed
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email',
@recipients = '[e-mail]',
@query = @query,                     
@query_attachment_filename = 'employees.csv',      
@body_format = 'HTML',
@subject = 'Test';

这篇关于使用 sp_send_dbmail 发送附件时出现错误无法初始化 sqlcmd 库，错误号为 -2147467259的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！