I have an issue with a report that was written before my time (I warn you it's got a lot of code and the error makes no sense). Basically its a stored procedure that creates a table variable and then inserts into it multiple times from different locations. Two of the inserts come from functions the other 3 come from just written sql. The problem is the first two in question run fine together in SSMS but in SSRS I keep getting a conversion error and I know it's from these two, the other 3 do not affect it whether they are included or not. Here is part of the procedure with just the two in question:
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
And these are the two functions that the above query reads from:
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
and
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
)
Both of these functions run fine independantly and both run fine as part of the first query however when i try run it in ssrs I get: An error occurred during the local report process. An error has occurred during report process. Query execution failed for the dataset 'gsp_rpt_InfoCaptured' Conversion failed when converting the varchar value '14-06-2015 14:21:18' to datatype smallint.
There are no conversions however. I've had similar problems in the past and oddly managed to get around them by moving the part which queries the questionnaire around the query however I cant do it this time.
I'm hoping someone can shed light on it for me. I have a feeling it's something to do with parts of the query being run before others have chance to complete. Ive tried using unions, table variable and CTEs but always come up to an error at some point. However I cannot use GO as I need variables for the report (Campaign and date time selections)
Thanks in advance for any help.
