ssrs 2008 report with truncation problem but no issue exists with sql in ssis manager

Question

ssrs 2008 report with truncation problem but no issue exists with sql in ssis manager

wendy elizabeth

SSCoach

Points: 16915
More actions
May 30, 2017 at 5:46 pm

#402106

In an existing ssrs 2008 report, I want to chage 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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63421 More actions · Answer 1

You do realize that the data types for your parameters and the field types and sizes in the underlying table don't match, right? If you want to pass multiple values to the stored procedure, you have to pass them as a table-valued parameter or use something like DelimitedSplit8K in your stored procedure to convert the delimited list passed by SSRS to the stored procedure.

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

Can you show me how to do both of your options? The table-valued parameter and/or use something like DelimitedSplit8K ?

pietlinden SSC Guru Points: 63421 More actions · Answer 3

Did you read Jeff Moden's article on DelimitedSplit8K?

wendy elizabeth SSCoach Points: 16915 More actions · Answer 4

Yes I did read his answer. However my IT manager does not want me to use that code.
Thus would you show me what you mean by 'table-valued parameter'?

pietlinden SSC Guru Points: 63421 More actions · Answer 5

He doesn't??? Jeff's code is about the safest stuff out there. What if you printed out the code for it and showed it to him? The code is commented so you can follow along what's going on. I'd print the entire article (and the code) and see if your boss will at least look at it.

here's Microsoft's definition of a TVP