执行sp_send_dbmail时遇sqlcmd库初始化错误-2147467259求助

执行查询时遇到以下错误：

Msg 22050, Level 16, State 1, Line 2
Failed to initialize sqlcmd library with error number -2147467259.

已完成以下排查：

• 确认SQL代理用户拥有足够权限；
• 已指定在msdb数据库中执行该命令；

请问是否需要传入@query_result_width参数？请求协助解决该问题。

附带执行的代码：

USE msdb
GO

EXEC sp_send_dbmail
      @profile_name = 'TESTSQL Database Mail',
      @recipients = 'Helpdesk@test.org',
      @subject = 'XXX Doc Entries Missing From COI Arch. Doc. Table',
      @body = 'The list of documents below are missing from the COI Archived Document Table.',
      @execute_query_database = 'msdb',
      @query =  '
SELECT 
     di.[id]
    ,di.[infotext]
    ,di.[creationtime]
    ,di.userstate
    ,ns.doctype
    ,ns.arcencldescr
    ,ns.arcenclcat 
    ,ns.referenceno
    ,ns.contactno
    ,ns.custno
    ,ns.docno
    ,ns.employno
    ,ns.jobno
    ,ns.positionno
    ,ns.purchreqno
    ,ns.vendno
INTO
    #navarchive
FROM 
    [BF1000].[dbo].[DocInfo] di
JOIN
    [BF1000].[dbo].[NaviStandard] ns ON di.id = ns.docid
WHERE
    di.userstate = 2502
    AND ns.referenceno like "AR%"
    AND arcenclcat <> "APP"
select
     na.id as "Doc. ID"
    ,na.referenceno as "Arch. Ref. No."
    ,na.infotext as "Doc. Info."
    ,na.doctype as "Doc. Type"
from
    #navarchive na
left join
    ESOERPNAV_Live.dbo.[ESO$COI Archived Doc_] ad
on
    na.id = ad.[COI Doc ID]
where
    ad.[COI Doc ID] is null
and
    na.id > 505745
order by
    na.id
drop table #navarchive              '

关于@query_result_width参数 #

这个参数确实可能触发该错误。当查询返回的结果行总宽度超过默认值（256字符）时，sqlcmd初始化会失败。你可以在sp_send_dbmail中添加该参数并设置一个足够大的值（比如1000或更大）来解决：

@query_result_width = 1000,

其他需要修正的问题 #

1. 字符串引号错误：SQL Server中字符串常量需使用单引号，你查询里的双引号（"AR%"、"APP"）会被识别为标识符，引发语法错误。全部替换为单引号：

   AND ns.referenceno like 'AR%'
   AND arcenclcat <> 'APP'
   

   列别名的双引号也建议换成方括号，避免潜在问题：

   na.id as [Doc. ID],
   na.referenceno as [Arch. Ref. No.],
   

2. 临时表作用域优化：虽然sp_send_dbmail的@query支持临时表，但改用CTE可以避免临时表的作用域风险，简化逻辑：

   WITH navarchive AS (
       SELECT 
            di.[id]
           ,di.[infotext]
           ,di.[creationtime]
           ,di.userstate
           ,ns.doctype
           ,ns.arcencldescr
           ,ns.arcenclcat 
           ,ns.referenceno
           ,ns.contactno
           ,ns.custno
           ,ns.docno
           ,ns.employno
           ,ns.jobno
           ,ns.positionno
           ,ns.purchreqno
           ,ns.vendno
       FROM 
           [BF1000].[dbo].[DocInfo] di
       JOIN
           [BF1000].[dbo].[NaviStandard] ns ON di.id = ns.docid
       WHERE
           di.userstate = 2502
           AND ns.referenceno like 'AR%'
           AND arcenclcat <> 'APP'
   )
   select
        na.id as [Doc. ID]
       ,na.referenceno as [Arch. Ref. No.]
       ,na.infotext as [Doc. Info.]
       ,na.doctype as [Doc. Type]
   from
       navarchive na
   left join
       ESOERPNAV_Live.dbo.[ESO$COI Archived Doc_] ad
   on
       na.id = ad.[COI Doc ID]
   where
       ad.[COI Doc ID] is null
   and
       na.id > 505745
   order by
       na.id
   

3. 邮件配置验证：再次确认TESTSQL Database Mail配置文件存在且可用，SQL代理账户拥有msdb数据库DatabaseMailUserRole的权限。

内容的提问来源于stack exchange，提问作者Maddy