问题描述
我需要通过脚本自动化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
推荐答案
这篇关于使用DB邮件通过T-SQL发送报告获取错误无法初始化sqlcmd库，错误号为-2147467259。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！