April 4, 2013 at 2:26 pm
in SSRS when i run a preview after selecting a sp aS data set I am getting an error
error converting data type nvarchar to int
THE FOLLOWINS THE SP IA M USING
@ProgramIDint = 0,
@Branchsmallint = 0,
@ActiveOnlybit = 1,
@CountyIDint = 0,
@ListingTypesmallint = 0,
@StartDatedate = null,
@EndDatedate = null,
@FundingIDint = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @ListingType = 3
Begin
Set @ActiveOnly = 0
End
SELECT ci.PeopleLinkID,
p.PersonID,
p.LastName,
p.FirstName,
p.MiddleName,
p.Address1,
p.Address2,
p.City,
p.State,
p.ZipCode,
p.Birthdate,
rl.Race,
p.Gender,
p.Phone,
p.Email,
pl.PeopleStart,
pl.PeopleEnd,
wi.County,
CASE WHEN pl.PeopleEnd IS NOT NULL THEN
(SELECT MAX(dbo.HistoryDateReasons.HistoryReason) AS TermReason
FROM dbo.HistoryDates INNER JOIN
dbo.HistoryDateReasons ON dbo.HistoryDates.HistoryReasonID = dbo.HistoryDateReasons.HistoryReasonID
WHERE (dbo.HistoryDates.HistoryTypeID = 5)
AND (dbo.HistoryDates.HistoryDateTo < GETDATE())
AND (dbo.HistoryDates.PeopleLinkID = pL.PeopleLinkID)
GROUP BY dbo.HistoryDates.PeopleLinkID)
ELSE NULL
END AS TermReason,
CASE WHEN pl.Branch = 30 THEN
(SELECT CONVERT(varchar(10), RunningBalance)
FROM vw_IncExpBalanceClient
WHERE PeopleLinkID = pl.PeopleLinkID)
ELSE fc.EligDescription
END AS Funding,
LastFirst AS StaffPerson,
p1.LastName + ', ' + p1.Firstname AS CaseWorker,
CASE WHEN p.Gender = N'F' THEN N'Female'
WHEN p.Gender = N'M' THEN N'Male'
ELSE N'Unknown'
END AS GenderName,
p.SSN,
ci.CaseNumber,
b.ProgramName AS Branch
FROMFundingCodes fc
RIGHT JOINOrgPeopleLink opl
RIGHT JOINClientInfo ci
INNER JOINPeopleLink pl
ON(ci.PeopleLinkID = pl.PeopleLinkID)
INNER JOINBranches b
ON(b.Branch = pl.Branch)
INNER JOINPeople p
ON(pl.PersonID = p.PersonID)
ON(opl.OrgPeopleLinkID = ci.CaseWorkerID)
LEFT JOINPeople p1
ON(opl.PersonID = p1.PersonID)
LEFT JOINWICounties wi
ON(p.CountyID = wi.CountyID)
LEFT JOINvw_StaffPick vp
ON(ci.StaffAssigned = vp.StaffID)
LEFT JOINRaceLookup rl
ON(p.RaceID = rl.RaceID)
ON fc.FundingID = ci.FundingID
WHERE (CASE WHEN @ProgramID > 0 AND @Branch > 0 AND pl.Branch = @Branch THEN 'T'
WHEN @ProgramID > 0 AND @Branch = 0 AND pl.Branch IN(SELECT Branch FROM Branches WHERE ProgramID = @ProgramID) THEN 'T'
ELSE 'F'
END = 'T')
AND (CASE WHEN @ActiveOnly = 1 AND
(pl.PeopleEnd IS NULL OR pl.PeopleEnd > @EndDate) AND
PL.CategoryCode <> 6 THEN 'T'
WHEN @ActiveOnly = 1 AND
PL.CategoryCode = 6 AND
pl.PeopleStart IS NOT NULL AND
(pl.PeopleEnd IS NULL OR pl.PeopleEnd > @EndDate) THEN 'T'
WHEN @ActiveOnly = 0 THEN 'T'
WHEN @ListingType = 3 THEN 'T'
ELSE 'F'
END = 'T')
AND (p.CountyID IN (@CountyID))
AND (CASE WHEN @ListingType = 0 AND (pl.PeopleStart <= @EndDate OR pl.PeopleStart IS NULL) THEN 'T'
WHEN @ListingType = 1 AND MONTH(pl.PeopleStart) = MONTH(@StartDate) THEN 'T'
WHEN @ListingType = 2 AND ((pl.PeopleStart BETWEEN @StartDate AND @EndDate) OR pl.PeopleStart IS NULL) THEN 'T'
WHEN @ListingType = 3 AND pl.PeopleEnd BETWEEN @StartDate AND @EndDate THEN 'T'
WHEN @ListingType = 4 AND pl.PeopleLinkID IN(SELECT PeopleLinkID FROM vw_PeopleActiveOnHold) THEN 'T'
ELSE 'F'
END = 'T')
AND (p.LastName IS NOT NULL)
AND (fc.FundingID In (@FundingID))
ORDER BY p.LastName, p.FirstName;
END
April 4, 2013 at 3:27 pm
without any test data, or specific line that the error occurs I dont think anyone here will be able to pin point you exactly where your exception occurs. My suggestion is to run this query on it own from query analyzer without putting it in a stored procedure that way at least you can see which line the conversion error occurs on.
--Provided that... all these variables are set with appropriate values that caused the error.
DECLARE @ProgramID int = 0
DECLARE @Branch smallint = 0
DECLARE @ActiveOnly bit = 1
DECLARE @CountyID int = 0
DECLARE @ListingType smallint = 0
DECLARE @StartDate date = null
DECLARE @EndDate date = null
DECLARE @FundingID int = 0
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @ListingType = 3
Begin
Set @ActiveOnly = 0
End
SELECT ci.PeopleLinkID,
p.PersonID,
p.LastName,
p.FirstName,
p.MiddleName,
p.Address1,
p.Address2,
p.City,
p.State,
p.ZipCode,
p.Birthdate,
rl.Race,
p.Gender,
p.Phone,
p.Email,
pl.PeopleStart,
pl.PeopleEnd,
wi.County,
CASE WHEN pl.PeopleEnd IS NOT NULL THEN
(SELECT MAX(dbo.HistoryDateReasons.HistoryReason) AS TermReason
FROM dbo.HistoryDates INNER JOIN
dbo.HistoryDateReasons ON dbo.HistoryDates.HistoryReasonID = dbo.HistoryDateReasons.HistoryReasonID
WHERE (dbo.HistoryDates.HistoryTypeID = 5)
AND (dbo.HistoryDates.HistoryDateTo < GETDATE())
AND (dbo.HistoryDates.PeopleLinkID = pL.PeopleLinkID)
GROUP BY dbo.HistoryDates.PeopleLinkID)
ELSE NULL
END AS TermReason,
CASE WHEN pl.Branch = 30 THEN
(SELECT CONVERT(varchar(10), RunningBalance)
FROM vw_IncExpBalanceClient
WHERE PeopleLinkID = pl.PeopleLinkID)
ELSE fc.EligDescription
END AS Funding,
LastFirst AS StaffPerson,
p1.LastName + ', ' + p1.Firstname AS CaseWorker,
CASE WHEN p.Gender = N'F' THEN N'Female'
WHEN p.Gender = N'M' THEN N'Male'
ELSE N'Unknown'
END AS GenderName,
p.SSN,
ci.CaseNumber,
b.ProgramName AS Branch
FROM FundingCodes fc
RIGHT JOIN OrgPeopleLink opl
RIGHT JOIN ClientInfo ci
INNER JOIN PeopleLink pl
ON ( ci.PeopleLinkID = pl.PeopleLinkID)
INNER JOIN Branches b
ON ( b.Branch = pl.Branch)
INNER JOIN People p
ON ( pl.PersonID = p.PersonID)
ON ( opl.OrgPeopleLinkID = ci.CaseWorkerID)
LEFT JOIN People p1
ON ( opl.PersonID = p1.PersonID)
LEFT JOIN WICounties wi
ON ( p.CountyID = wi.CountyID)
LEFT JOIN vw_StaffPick vp
ON ( ci.StaffAssigned = vp.StaffID)
LEFT JOIN RaceLookup rl
ON ( p.RaceID = rl.RaceID)
ON fc.FundingID = ci.FundingID
WHERE (CASE WHEN @ProgramID > 0 AND @Branch > 0 AND pl.Branch = @Branch THEN 'T'
WHEN @ProgramID > 0 AND @Branch = 0 AND pl.Branch IN(SELECT Branch FROM Branches WHERE ProgramID = @ProgramID) THEN 'T'
ELSE 'F'
END = 'T')
AND (CASE WHEN @ActiveOnly = 1 AND
(pl.PeopleEnd IS NULL OR pl.PeopleEnd > @EndDate) AND
PL.CategoryCode <> 6 THEN 'T'
WHEN @ActiveOnly = 1 AND
PL.CategoryCode = 6 AND
pl.PeopleStart IS NOT NULL AND
(pl.PeopleEnd IS NULL OR pl.PeopleEnd > @EndDate) THEN 'T'
WHEN @ActiveOnly = 0 THEN 'T'
WHEN @ListingType = 3 THEN 'T'
ELSE 'F'
END = 'T')
AND ( p.CountyID IN (@CountyID))
AND (CASE WHEN @ListingType = 0 AND (pl.PeopleStart <= @EndDate OR pl.PeopleStart IS NULL) THEN 'T'
WHEN @ListingType = 1 AND MONTH(pl.PeopleStart) = MONTH(@StartDate) THEN 'T'
WHEN @ListingType = 2 AND ((pl.PeopleStart BETWEEN @StartDate AND @EndDate) OR pl.PeopleStart IS NULL) THEN 'T'
WHEN @ListingType = 3 AND pl.PeopleEnd BETWEEN @StartDate AND @EndDate THEN 'T'
WHEN @ListingType = 4 AND pl.PeopleLinkID IN(SELECT PeopleLinkID FROM vw_PeopleActiveOnHold) THEN 'T'
ELSE 'F'
END = 'T')
AND (p.LastName IS NOT NULL)
AND (fc.FundingID In (@FundingID))
ORDER BY p.LastName, p.FirstName;
April 4, 2013 at 3:38 pm
There no error in code since if i add the script as dataset my report is running perfect without any errors
only when i select dataset as sp and running the report i am getting this error
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply