使用DB邮件通过T-SQL发送报告获取错误无法初始化sqlcmd库，错误号为-2147467259。 [英] Using DB mail to send an Report via T-SQL getting Error Failed to initialize sqlcmd library with error number -2147467259.

问题描述

我需要通过脚本自动化Always ON的仪表板报告，脚本很整洁，但我打算通过SQL代理作业来安排脚本并使用SMTP和数据库邮件发送报告。 

I have an requirement to automate the dashboard report of Always ON via an script , the script is neat but i am planning to schedule the script via an SQL agent job and use SMTP and Database mail to send the report. 

以下是我通过许多链接收到相同错误但是更改  的任何解决方案的代码

Below is the code I went through many links on people receiving the same error but any of the solution in changing the  

我使用数据库邮件发送了一封测试电子邮件，效果很好，因此SMTP显示功能正常。

I sent an Test email using the DB mail and it works well so SMTP appears functional.

@ query_result_header参数或调整var char max等..对我来说没有用，当我解析时我没有得到一个语法错误，我不确定是否将
要求以附件形式将报告作为CSV获取功能..如果我能够发生这种情况会有所帮助，下面是代码 

SQL2016
SP2
企业版：基于核心的许可（64位）
13.0.5153.0

==== ================================================== ===================

=========================================================================

DECLARE @sub VARCHAR（100）;

DECLARE @qry VARCHAR（1000）;

DECLARE @msg VARCHAR（250）;

DECLARE @query NVARCHAR（1000）;

DECLARE @query_attachment_filename NVARCHAR（520）;
$
SELECT @sub ='请参阅附件AG集团控制板报告XXXX';

SELECT @msg ='请参阅AG仪表板报告的附加电子表格'; $
SELECT @query ='



select cluster_name，



quorum_state_desc


来自sys.dm_hadr_cluster的




转到



选择ar.replica_server_name，



ars.role_desc，

¥ b $ b ar.failover_mode_desc，



ars.synchronization_health_desc，



ars.operational_state_desc，



CASE ars.connected_state



WHEN 0那么''断开''


当1'那么'连接''¥b $ b

ELSE''


END as ConnectionState



来自sys.dm_hadr_availability_replica_states ars



内部连接sys.availability_replicas ar on ars。 replica_id = ar.replica_id



和ars.group_id = ar.group_id



go





选择不同的rcs.database_name，



ar.replica_server_name，



drs.synchronization_state_desc，



drs.synchronization_health_desc，



CASE rcs.is_failover_ready



WHEN 0那么'数据丢失''


当1'没有数据丢失''



ELSE''


END为FailoverReady



来自sys.dm_hadr_database_replica_states drs



内连接sys.availability_replicas ar on drs.replica_id = ar.replica_id



和drs.group_id = ar.group_id



内连接sys.dm_hadr_database_replica_cluster_states rcs on drs.replica_id = rcs.replica_id



order by replica_server_name'



SELECT @query_attachment_filename ='AG.csv';



    EXEC msdb.dbo.sp_send_dbmail

      @profile_name ='XXXXX'，

      @recipients =" XXXXXg"，

@body = @msg，

      @subject = @sub，



      @query = @query，

@query_attachment_filename = @query_attachment_filename，

      @attach_query_result_as_file = 1，

      @query_result_header = 1，

@query_result_width = 256，

      @query_result_separator =''，

      @query_result_no_padding = 1;

$


b

DECLARE @sub VARCHAR(100);
DECLARE @qry VARCHAR(1000);
DECLARE @msg VARCHAR(250);
DECLARE @query NVARCHAR(1000);
DECLARE @query_attachment_filename NVARCHAR(520);
SELECT @sub = 'REFER TO THE ATTACHED AG GROUP DASHBOARD REPORT FOR XXXX';
SELECT @msg = 'Please refer to the attached spread sheet for the AG Dashboard report';
SELECT @query = '

select cluster_name,

quorum_state_desc

from sys.dm_hadr_cluster

go

select ar.replica_server_name,

ars.role_desc,

ar.failover_mode_desc,

ars.synchronization_health_desc,

ars.operational_state_desc,

CASE ars.connected_state

WHEN 0 THEN ''Disconnected''

WHEN 1 THEN ''Connected''

ELSE ''

END as ConnectionState

from sys.dm_hadr_availability_replica_states ars

inner join sys.availability_replicas ar on ars.replica_id = ar.replica_id

and ars.group_id = ar.group_id

go

select distinct rcs.database_name,

ar.replica_server_name,

drs.synchronization_state_desc,

drs.synchronization_health_desc,

CASE rcs.is_failover_ready

WHEN 0 THEN ''Data Loss''

WHEN 1 THEN ''No Data Loss''

ELSE ''

END as FailoverReady

from sys.dm_hadr_database_replica_states drs

inner join sys.availability_replicas ar on drs.replica_id = ar.replica_id

and drs.group_id = ar.group_id

inner join sys.dm_hadr_database_replica_cluster_states rcs on drs.replica_id = rcs.replica_id

order by replica_server_name '

SELECT @query_attachment_filename = 'AG.csv';

   EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'XXXXX',
     @recipients = "XXXXXg",
@body = @msg,
     @subject = @sub,

     @query = @query,
@query_attachment_filename = @query_attachment_filename,
     @attach_query_result_as_file = 1,
     @query_result_header = 1,
@query_result_width = 256,
     @query_result_separator =' ',
     @query_result_no_padding = 1;


BR

Eben

推荐答案

用于SQL Server代理的数据库邮件配置文件需要是msdb数据库中的DatabaseMailUserRole的成员

The Database Mail Profile that is used for the SQL server agent need to be a member of DatabaseMailUserRole in the msdb database

这篇关于使用DB邮件通过T-SQL发送报告获取错误无法初始化sqlcmd库，错误号为-2147467259。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！