Ssrs 2008 report sql truncation error but no error in ssis manager

SSAS and SSRS
1

In an existing ssrs 2008 report, I want to change a hard coded value of 'P' to use a parameter value @serviceType.

When I execute the following sql in ssis 2012 manager, it runs correctly:
DECLARE @calendarID INT, @endYear SMALLINT
,@personID INT
, @serviceType varchar(10)
SET @endYear = 2017
SET @calendarID = (SELECT calendarid FROM TEST.dbo.Calendar WHERE name LIKE '%ash%' AND endYear = @endYear AND summerSchool = 1) --6153
SET @serviceType='S'
set @personID=999999
--end debug

IF OBJECT_ID('tempdb..#Homeroom') IS NOT NULL DROP TABLE #Homeroom

CREATE TABLE #Homeroom (personID INT,
studentNumber VARCHAR(15),
serviceType VARCHAR(1),
enrollmentID INT,
calendarID INT,
schoolID INT,
teacherPersonID INT,
teacherFirstName VARCHAR(50),
teacherMiddleName VARCHAR(50),
teacherLastName VARCHAR(50),
teacherName VARCHAR(104),
sectionTeacherDisplay VARCHAR(102),
roomID INT,
roomName VARCHAR(10),
courseID INT,
courseHomeroom BIT,
sectionID INT,
sectionHomeroom BIT,
rosterID INT,
trialID INT,
periodID INT,
periodScheduleID INT,
structureID INT,
termID INT)

INSERT INTO #Homeroom
EXEC CampusTEST.dbo.spHomeroom @endYear, @calendarID, @serviceType, @personID

Note:Here is how the @serviceType field is defined in the stored procedure:

CREATE PROCEDURE [dbo].[spHomeroom] (@endYear SMALLINT,
@calendarID VARCHAR(8000),
@serviceType VARCHAR(10),
@personID VARCHAR(8000))

However when I run the ssrs 2008 report setting the @servicetype parameter value to a 'text' field. I get truncation messages. The message I get is
'query execution failed for 'StudentsForReport' String or binary data would be truncated'

Thus can you tell me what I can do to the SSRS 2008 report so that it will run correctly?

2

Longshot:

That relies on the @Parameters being listed in the same order as the Sproc definition. As per the code you've provided that is all fine, but maybe there is something adrift when SSRS calls that Sproc ... and if not now then someone could change something [in the Sproc definition] in the future.

Explicitly naming the parameters would avoid that possibility:

EXEC CampusTEST.dbo.spHomeroom @endYear=@endYear, @calendarID=@calendarID,  @serviceType=@serviceType, @personID=@personID

I know nothing about SSRS, so can't help directly with that, but it seems unlikely that something defined as 'text' but actually containing only a 1-char string would cause a problem being assigned to a VARCHAR(10) , hence why I thought that the issue might be the sequence of the parameters - i.e. accidentally something is trying to assign one of the VARCHAR(8000) parameters into @serviceType

3

Why are you creating a temp table in an SSRS dataset? What are you doing with that temp table after creating and populating it?

Ideally - you should just set the dataset to the stored procedure and refresh the fields. This will create the parameters for you and then you will have SSRS parameters that you can prompt the user to fill out. If you don't want the users to do that - then set them to internal and provide the value you want.

One more item - how does this tie to SSIS or did you mean SSMS (SQL Server Management Studio)?

1 Like