sql - SSIS 错误:无法从 SQL 命令的参数中检索目标列描述
问题描述
我收到一个错误:
[将更新的记录加载到 Table_vwAs_FixedIdentifier [85]] 错误:无法从 SQL 命令的参数中检索目标列描述。
当我在图像中执行 SSIS 任务时。它在左下角“将更新的记录加载到 Table_vwAs..” oleDb 更新任务上失败。它具有以下 sql 语句,并且还具有如下所示的触发器。到目前为止,我无法确定此问题的原因。当我禁用触发器时,任务成功运行。任何帮助都会让人大开眼界。
UPDATE
[dbo].[Sec]
SET
[SecID]=?
,[SECURITY_NAME]=?
,[SECURITY_SECTYPE_ID]=?
,[SECURITY_EXCHANGE_ID]=?
WHERE
[MasterID]=?
触发代码如下:
alter trigger [dbo].[TrTable_Fixed_AFTERUPDATE] on [dbo].[Sec]
after insert ,update
as
set NOCOUNT on
declare @DBMailDistributionListId varchar(50)
declare @ProfileName varchar(max)
declare @Recipients varchar(max)
declare @CopyRecipients varchar(max)
declare @BlindCopyRecipients varchar(max)
set @DBMailDistributionListId = 'Crossadyne1'
set @ProfileName = SharedCode.dbo.FnGetDBMailDistributionListProfileName(@DBMailDistributionListId)
set @Recipients = SharedCode.dbo.FnGetDBMailDistributionListRecipients(@DBMailDistributionListId)
set @CopyRecipients = SharedCode.dbo.FnGetDBMailDistributionListCopyRecipients(@DBMailDistributionListId)
set @BlindCopyRecipients = SharedCode.dbo.FnGetDBMailDistributionListBlindCopyRecipients(@DBMailDistributionListId)
declare @Subject varchar(max)
declare @Body varchar(max)
set @Body = ''
select distinct @Body = @Body + 'Master Sec ID: ' + convert(nvarchar(50) ,i.MasterId) + char(13) + char(10)
+ 'New Legal Entity ID: ' + convert(nvarchar(50) ,i.[INTERNAL_SECURITY_LEGALENTITY_ID])
from INSERTED i
inner join DELETED d
on i.MasterId = d.MasterId
where i.[INTERNAL_SECURITY_LEGALENTITY_ID] <> d.[INTERNAL_SECURITY_LEGALENTITY_ID]
and i.Security_Change_Date = i.LegalEntityChangeDate
and i.LegalEntityChangedBy not in (2 ,5)
if (@Body <> '')
begin
set @Subject = 'WARNING: Crossadyne Security Legal Entity changed'
exec msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName
, @recipients = @Recipients
, @copy_recipients = @CopyRecipients
, @blind_copy_recipients = @BlindCopyRecipients
, @subject = @Subject
, @body = @Body
end
set @DBMailDistributionListId = 'test'
set @ProfileName = SharedCode.dbo.FnGetDBMailDistributionListProfileName(@DBMailDistributionListId)
set @Recipients = SharedCode.dbo.FnGetDBMailDistributionListRecipients(@DBMailDistributionListId)
set @CopyRecipients = SharedCode.dbo.FnGetDBMailDistributionListCopyRecipients(@DBMailDistributionListId)
set @BlindCopyRecipients = SharedCode.dbo.FnGetDBMailDistributionListBlindCopyRecipients(@DBMailDistributionListId)
set @Body = ''
select distinct @Body = @Body + 'Master ID: ' + convert(nvarchar(50) ,i.MasterId) + char(13) + char(10)
+ 'Previous Legal Entity ID: ' + convert(nvarchar(50) ,d.[INTERNAL_SECURITY_LEGALENTITY_ID])
from INSERTED i
inner join DELETED d
on i.MasterId = d.MasterId
where i.[INTERNAL_SECURITY_ID] <> d.[INTERNAL_SECURITY_LEGALENTITY_ID]
if (@Body <> '')
begin
set @Subject = 'WARNING: Crossadyne Security Legal Entity changed'
exec msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName
, @recipients = @Recipients
, @copy_recipients = @CopyRecipients
, @blind_copy_recipients = @BlindCopyRecipients
, @subject = @Subject
, @body = @Body
end
set NOCOUNT off
解决方案
该问题是由于
exec msdb.dbo.sp_send_dbmail
在触发器代码中引起的,因为它没有在 QA 服务器上配置。在我评论这些行后解决。
推荐阅读
- memory - Vulkan 是否支持本地子组内存共享和操作?
- blockchain - 如何模拟在 Ganache 本地区块链上铸造代币的过程?
- python - 努力将数据添加到 PostgreSQL 表
- google-cloud-dataprep - 为什么当我从 Dataprep 到 GBQ 运行作业时,结果只有一行?
- python - Python Discord 添加角色
- node.js - 我正在尝试为数据库播种,但 Sequelize 出现问题:无法打开引用的表“未定义”
- node.js - NGINX 一直显示同一个页面
- typescript - “any”类型的参数不能分配给“never”类型的参数 - Vue
- r - 如何计算 Newman (2010) 中描述的正则等价的“Katz 相似性”度量
- r - 重塑数据框 R:一些变量从宽到长格式,一些从长到宽