☰

SSRS 报告因转换错误而失败但在 ssms 中运行良好？

tag-icon • tag-icon sql ssrs
SSRS 报告因转换错误而失败但在 ssms 中运行良好？

我对一份在我之前编写的报告有疑问（我警告你，它包含大量代码，错误毫无意义）。基本上，它是一个存储过程，创建一个表变量，然后从不同位置多次插入到其中。两个插入来自函数，其他三个来自刚编写的 SQL。问题是，前两个在 SSMS 中一起运行良好，但在 SSRS 中，我一直收到转换错误，我知道这是来自这两个，其他三个无论是否包含在内都不会影响它。以下是仅包含两个问题的程序的一部分：

declare @QTable TABLE (
ChangeMadeTo Varchar(255) NULL,
EntryDate DateTime NULL,
Updatingagent Varchar(255) NULL,
ColumnName Varchar(255) NULL,
CompanyID Varchar(255) NULL,
CompanyName Varchar(255) NULL,
LastNumberCalled Varchar(15) NULL,
CallNotes Varchar (4000) NULL,
OldValue Varchar(255) NULL,
NewValue Varchar(255) NULL)

insert into @QTable (
ChangeMadeTo, EntryDate, Updatingagent, ColumnName, CompanyID, CompanyName, LastNumberCalled, CallNotes,  OldValue, NewValue
)

SELECT 
'Questionnaire'
, Q.DateTimeStamp
, Agentforename + ' ' + AgentSurname + ' ('+ UserID + ')'
, QuestionText
, cm.companyid
, companyname
, cn.PhoneNumber
, CAST(ISNULL(NULLIF(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CallNotes, '|', ''), '"', ''), char(9), ' '), char(10), ' '), char(13), ' '), ''), '') AS VARCHAR(4000)) AS CallNotes
, 'N/A'
, Q.Answer
from dbo.gfn_Questionniare_MC_And_FreeText() as Q
join callcentre_web.dbo.Agents as A
on A.AgentID = Q.AgentID
join campaignmapping as cm
on cm.companyid = Q.Company_ID
and cm.campaignid = Q.campaignid
join company as C
on c.companyid = cm.companyid
join Calldetails as CD
on cd.mapid = cm.mapid
left join contactnumbers as cn
on cn.contactnumberid = cd.contactnumberid
Where Q.CampaignID = @CampaignID
and Q.DateTimeStamp between @fromdate and @todate



insert into @QTable (
ChangeMadeTo, EntryDate, Updatingagent, ColumnName, CompanyID, CompanyName, LastNumberCalled, CallNotes,  OldValue, NewValue
)

SELECT 
'Questionnaire'
, Q.DateTimeStamp
, Agentforename + ' ' + AgentSurname + ' ('+ UserID + ')'
, QuestionText
, cm.companyid
, companyname
, cn.PhoneNumber
, CAST(ISNULL(NULLIF(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CallNotes, '|', ''), '"', ''), char(9), ' '), char(10), ' '), char(13), ' '), ''), '') AS VARCHAR(4000)) AS CallNotes
, 'N/A'
, Q.Answer
from dbo.gfn_Questionniare_Multiple_Selections() as Q
join callcentre_web.dbo.Agents as A
on A.AgentID = Q.AgentID
join campaignmapping as cm
on cm.companyid = Q.Company_ID
and cm.campaignid = Q.campaignid
join company as C
on c.companyid = cm.companyid
join Calldetails as CD
on cd.mapid = cm.mapid
left join contactnumbers as cn
on cn.contactnumberid = cd.contactnumberid
Where Q.CampaignID = @CampaignID
and Q.DateTimeStamp between @fromdate and @todate

这是上述查询读取的两个函数：

SELECT 
  cm.campaignid
, campaign
, cm.CompanyID as Company_ID
, QuestionText
, DateTimeStamp
, AgentID
, CAST(STUFF((select ',' + CAST(ISNULL(NULLIF(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(e.Answer, '|', ''), '"', ''), char(9), ' '), char(10), ' '), char(13), ' '), ''), '') AS VARCHAR(4000))
        FROM Questionnaire a
        cross apply dbo.SplitString(a.Answer,',') b 
        join CallCentre_Web.dbo.Questionnaire_Answers e 
        on b.s = e.AnswerID
        where isnumeric(b.s) = 1 
        and QuestionTypeID = 2 
        and a.QuestionnaireID = q.QuestionnaireID
        for xml path('')),1,1,'') AS VARCHAR(255)) as Answer
FROM Questionnaire AS q
JOIN dbo.ProfileMapping AS PM
ON PM.ProfileMappingID = Q.ProfileMappingID
JOIN CampaignMapping AS CM
ON PM.MapID = CM.MapID
join callcentre_web.dbo.LK_ProfileType as PT
on PT.ProfileTypeID = PM.ProfileTypeID
JOIN CallCentre_Web.dbo.Questionnaire_Questions QQ
ON Q.QuestionID = QQ.QuestionID
join callcentre_web.dbo.lk_Campaigns as LKC
on LKC.CampaignID = cm.CampaignID
WHERE QuestionTypeID = 2

和

SELECT
  cm.campaignid
, Campaign
, CompanyID as Company_ID
,QuestionText
,CAST(ISNULL(NULLIF(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Q.Answer, '|', ''), '"', ''), char(9), ' '), char(10), ' '), char(13), ' '), ''), '') AS VARCHAR(4000)) as Answer
, DateTimeStamp
, AgentID
FROM Questionnaire AS Q
JOIN CallCentre_Web.dbo.Questionnaire_Questions QQ
ON Q.QuestionID = QQ.QuestionID
JOIN dbo.ProfileMapping AS PM
ON PM.ProfileMappingID = Q.ProfileMappingID
join callcentre_web.dbo.LK_ProfileType as PT
on PT.ProfileTypeID = PM.ProfileTypeID
JOIN CampaignMapping AS CM
ON PM.MapID = CM.MapID
join callcentre_web.dbo.lk_Campaigns as LKC
on LKC.CampaignID = cm.CampaignID
WHERE QuestionTypeID = 0

union all
SELECT 
    cm.campaignid
, Campaign
, CompanyID
, QuestionText
, CAST(ISNULL(NULLIF(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(QA.Answer, '|', ''), '"', ''), char(9), ' '), char(10), ' '), char(13), ' '), ''), '') AS VARCHAR(4000)) AS Answer
, DateTimeStamp, AgentID
FROM Questionnaire AS Q
JOIN CallCentre_Web.dbo.Questionnaire_Questions QQ
ON Q.QuestionID = QQ.QuestionID
JOIN CallCentre_Web.dbo.Questionnaire_Answers QA
ON Q.Answer = QA.AnswerID
JOIN dbo.ProfileMapping AS PM
ON PM.ProfileMappingID = Q.ProfileMappingID
join callcentre_web.dbo.LK_ProfileType as PT
on PT.ProfileTypeID = PM.ProfileTypeID
JOIN CampaignMapping AS CM
ON PM.MapID = CM.MapID
join callcentre_web.dbo.lk_Campaigns as LKC
on LKC.CampaignID = cm.CampaignID
WHERE QuestionTypeID = 1
)

这两个函数独立运行良好，并且作为第一个查询的一部分运行良好，但是当我尝试在 ssrs 中运行它时，我得到：本地报告过程中发生错误。报告过程中发生错误。数据集“gsp_rpt_InfoCaptured”的查询执行失败将 varchar 值“14-06-2015 14:21:18”转换为数据类型 smallint 时转换失败。

但是没有转换。我以前也遇到过类似的问题，奇怪的是，我设法通过将查询问卷的部分移到查询周围来解决这些问题，但这次我做不到。

我希望有人能帮我解释一下。我觉得这与查询的某些部分在其他部分完成之前运行有关。我尝试过使用 unions、表变量和 CTE，但总是在某个时候出错。但是我不能使用 GO，因为我需要报告的变量（活动和日期时间选择）

在此先感谢您的帮助。

答案1

当我的存储过程通过 SSRS 运行而不是在 SSMS 中运行时，我遇到了类似的问题，弹出转换错误。

当我确保存储过程没有发出任何警告时，错误就消失了。例如

警告：空值已被聚合或其他 SET 操作消除。

不确定它是否有帮助但值得一试。

相关内容